This tag is for the 2005 version of Microsoft's SQL Server.
2
votes
3answers
97 views
How can I query data from a linked server, and pass it parameters to filter by?
I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned.
The basic syntax looks something like this:
INSERT INTO #tmpTable (Id, ...
1
vote
3answers
169 views
Why is SQL running the same query longer from another connection?
Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely?
Dont jump to any conclusion just yet, let me explain what I mean...
Here is the setup:
A Windows ...
0
votes
2answers
139 views
SQL Server replication subscriptions marked as inactive
Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever?
It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription ...
7
votes
1answer
202 views
Hard limitation for merge replication article count?
A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article ...
4
votes
1answer
85 views
Process attempted to unlock a resource it does not own
SQL Server 2005 SP4 32-Bit
I have a DBCC CHECKDB job running nightly. Last night, soon after the job started, I got the errors below. The database is NOT in suspect mode, and CHECKDB comes back clean ...
3
votes
1answer
132 views
SQL Server 2005 Replication
I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription.
server 1 windows 2003 server 192.168.10.1 connected ...
1
vote
1answer
82 views
SQL Server 2005 error while starting service
I am getting error while starting the service & errorlog is:
2013-04-04 14:50:49.14 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) ...
1
vote
1answer
170 views
how to add attachment(text file) to database mail?
I have scenario
Daily i run a sql job to apply a new updates to one table - this job will create one text file daily - text file contains all new updates
I can send a mail to client that job is ...
1
vote
1answer
50 views
User login error when trying to access secured SQL Server database
We have a username that was recently renamed from one username to another (think getting married). The Active Directory admin renamed the user because "it has always worked in the past".
One vendor ...
0
votes
1answer
143 views
FETCH API_CURSOR causing open transaction in tempDB
A select statement run from Oracle 11gR1 to SQL Server 2005 using Gateway leaves an open transaction in tempdb. The user is a datareader in Database XYZ. Not sure why the open tran is in tempDB when ...
4
votes
0answers
290 views
Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100
I have a SQL Server 2008 R2 server with a bunch of published databases that are currently operating under compatibility level 90 (2005).
The subscription databases are also SQL Server 2008 R2, ...
3
votes
0answers
34 views
SQL Server 2005 not collecting logins in the log file
I am currently running SQL Server 2005 Standard Edition on a Windows 2003 server machine.
I have gone to the properties of the server and confirmed that the Login Auditing is set to both failed and ...
3
votes
0answers
210 views
TempDB Contention with 24 physical tempdb files on 48 core AMD Opteron box
I just received the following notification about an instance of SQL Server 2005. The instance has a core-to-tempdb-file ratio of 2:1 for a total tempdb file count of 24 files. Contention should not ...
3
votes
0answers
165 views
Deny view definition at DB level and symmetric keys MS SQL Server 2005
I don't want a restricted database role to be able to view table and stored procedure definitions and code.
So I do
deny view definition to some_db_role
It does appear though that this is not ...
3
votes
0answers
179 views
Merge replication sync fails after ALTER TABLE… NOT NULL
We have a SQL Server 2005 database that uses merge replication to sync to SQL Compact Edition subscriptions. We added a column to a table using:
ALTER TABLE Shifts ADD COLUMN StateOrProvince NOT NULL ...
2
votes
0answers
17 views
ODBC - how to set up ODBC driver for 2 languages (for different code page)
How to set up an ODBC driver to operate the Polish code page (1250) and Lithuania (1257) at the same time? Although the code page Lithuanian is wider than the Polish code page, the driver incorrectly ...
2
votes
0answers
30 views
How can service pack rollback file end up on non-system volume?
I'm attempting to upgrade a SQL 2005 SP3 clustered instance to SP4. During installation, a rollback event took place with the error indicating there was insufficient space on the the disk. The system ...
2
votes
0answers
126 views
Can 2 tables lock each other if concurrent read/update do not affect same rows? pagelocks off
Can 2 tables cause a deadlock, if the pagelocks are off, and the rows are not related.
E.g.
Query 1 runs for a few seconds
Select * from Orders where CustomerID = 1 and DateOrdered between @D1 and ...
2
votes
0answers
66 views
Is it normal for tempdb TPS to be higher than all other databases combined?
I'm investigating some performance issues on a production DB here, and I've noticed a slightly odd thing that I'm not sure is weird or not.
If I run a quick Performance Monitor trace for the ...
2
votes
0answers
92 views
CREATE ASSEMBLY - EXCEPTION_ACCESS_VIOLATION
I am trying to create an assembly on my SQL Server 2005 server. Here is what I am using:
CREATE ASSEMBLY [SQLCLRTest]
AUTHORIZATION [dbo]
FROM 'path\to\my\file.dll'
WITH PERMISSION_SET = ...
2
votes
0answers
184 views
I/O is frozen on database databasename
On SQL Server 2005 log report, everyday I receive the following message:
I/O is frozen on database databasename.
Is it serious ?
How to resolve it ?
2
votes
0answers
87 views
Lock timeout on user tables traced back to system processes
I have a problem where users are reporting intermittent errors in an application that is using a SQL Server 2005 database. Nothing jumped out at me at first glance so I took a look at ...
2
votes
0answers
573 views
823 errors on tempdb
Have a couple Fiber channel SAN-attached cluster servers which have been intermittently reporting errors like:
2012-05-09 16:31:54.13 spid74 Error: 823, Severity: 24, State: 2.
2012-05-09 ...
2
votes
0answers
262 views
Logic to convert a UTC date-time to a specific time zone
I have a table which has 1 row per event. Each event has a datetime stamp column which has values in UTC time. Each row has an associated local timezone in which the event occurred.
I have a DIM ...
2
votes
0answers
77 views
MS-SQL index not using all the available columns as seek predicates
I have a sql query on a large table (>17 million rows) that some times does not populate all the predicates before performing index seek, which results in not filtering out enough rows and slowing the ...
1
vote
0answers
44 views
When converting a table valued function to inline, why do I get a lazy spool?
I have a table valued function that I want to convert to inline to improve performance.
It sets the value of variables to the value four bit columns in one row of a table (looking up the row using a ...
1
vote
0answers
57 views
Have @Table1 with values to update into @Table2, can I update @Table1 with @Table2.@@Identity? or Cursor?
I have 2 tables:
declare @Customer_Links table (
Customer_LinkID int identity(1,1),
CustomerID int
)
declare @Customer_Regions table (
CustomerRegionID int identity(1,1),
Customer_LinkID int,
...
1
vote
0answers
165 views
FOR XML EXPLICIT problems in SQL Server 2005 Express
SQL Server 2005 Express Service Pack 2 w/Advanced Services
Windows 2003 R2
I recently migrated a database from MSDE 2000 to MSSQL 2005 that is used for a tourist website that can be displayed in one ...
1
vote
0answers
69 views
SQL server Load Balancing
SQL server user access cut down from 8pm - 9am.
During this period our SSIS scripts would run to load data(from Oracle/mainframe/Access) to sql, large database(s) & too much data - mortgage ...
1
vote
0answers
178 views
SQL Server 2005 Memory Pressure
We keep having a nagging problem with our SQL Server. I don't know what changed, but we started having out of memory errors every 3 weeks or so.
I thought I could locate something strange with DBCC ...
1
vote
0answers
67 views
summary column in ssrs2005 matrix
Hi I create a monthly sale report. there is a week-to-date column at the end of each week, then the end of report, I need to a month-to-date column. The the column will display date and the row will ...
1
vote
0answers
1k views
Error Authenticating Proxy Account when Executing SSIS job
I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages.
These packages have been running for years and the associated steps run via a Proxy Account ...
1
vote
0answers
111 views
Database model problem from MS Access to SQL Server 2005
Good Day,
I am a C# programmer but I have a problem with a database model on SQL Server 2005. I have a client who told me to rebuild his database from Access to SQL Server 2005 and actually I did but ...
1
vote
0answers
98 views
How do I use MDX scripting in SQL server 2005 Business Intelligence Development Studio to create the following Member?
I am using Microsoft SQL server 2005 Business Intelligence Development Studio to build a cube.
I need to calculate a member called INCREMENTAL OVERTIME. INCREMENTAL OVERTIME is based off of the ...
1
vote
0answers
54 views
Tool to list down resultset from SQL Server for bulk data
I have many records with me for which I need to query the database. How can I write a query that can list down all the resultset at once using my large set of data?
The database that I used in SQL ...
1
vote
0answers
160 views
Update excel sheets from SQL Server 2005 linked servers
I use SQL Server linked servers to connect to Excel 2003 multiple tabs workbook. After connected, I have not problem to use select statement like:
select * FROM OPENQUERY(TEST, 'select * from [fist ...
1
vote
0answers
1k views
Schannell: Certificate received from the remote server was issued by an untrusted certificate authority
i'm not sure whether to ask this on ServerFault or here.
Periodically following error is logged in Windows Server 2008 Administrative events:
The certificate received from the remote server was ...
1
vote
0answers
265 views
Can the service-broker cause SQL Server to crash when using SQLDependency
I have C# application that uses SQLDependency to get updates from a message table. I have tested it quite extensively and worked great in the development environment but when I ran it on the ...
0
votes
0answers
78 views
DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'
I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
0
votes
0answers
43 views
sql server 2005 profiler
We have a overnight agent job and one of the steps runs particular slow recently. The step calls a stored procedure. I used sql server profiler to capture the trace for that proc into a table. I ...
0
votes
0answers
43 views
Invalid Object SQL Server 2005 Express
I have 2 SQL Servers, let's call them A and B. I have a ColdFusion app that runs on server A. It connects to my database, MyDb. I needed to update the db content so I backed up the same db on server B ...
0
votes
0answers
33 views
Email alerts and Server Side Tracing
This question is in regards to SQL 2005 and email alerts on long running queries.
Does this sound like the best approach (with the least impact to the server) to receiving alerts to long running ...
0
votes
0answers
38 views
SQL Server 2000 database attach error to SQL Server 2005
I have a SQL Server 2000 database (mdf and ldf).
When I try to attach it, using SQL Server Management Studio Express 2005, I get this error:
Attach database failed for Server 'XXXXX\SQLEXPRESS'.
...
0
votes
0answers
88 views
Is my SQL Server 2005 Maintenance Plan running twice a day?
I'm a new DBA trying to discover how a production Reports server is setup. I did find a Maintenance Plan. It does show to be scheduled to run in a Job at 1am each day.
Here's my dilemma: When I ...
0
votes
0answers
10 views
User selects to view Report by Units/Empl/State- how to optimize query when selecting U/E/S (3 if statements)
I have a stored procedure in which the user selects to view sales by either Units / Employees/ State (in the real application there are 10 options)
This inserts into a #temp table via the following ...
0
votes
0answers
46 views
Error message installing SQL Server 2005 Enterprise on my Windows 7 Enterprise
I have a problem installing SQL Server 2005 on my Windows 7 Enterprise 32 bit operating system.
The installation never completes. The error log file states I need to have IIS installed (I do and it ...
0
votes
0answers
102 views
How do I get the date that each user last logged onto the server / database in SQL Server 2005
Currently, I have the following code that gets the DBNAME, USERNAME, ROLE_NAME for each user on the database. I need to get the last logged in date for each user to fill in the
LASTLOGIN_DATE.
ALTER ...
