This tag is for SQL Server 2005, major build version 9.00.xxxx.

learn more… | top users | synonyms (2)

5
votes
0answers
37 views

Error in partition switching in Sql Server 2014 when migrating a database from Sql Server 2005

I am migrating an old database from SQL Server 2005 to SQL Server 2014. This database has a partitioned table; a scheduled job periodically moves old data from the "online" table to the the historical ...
3
votes
0answers
363 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
238 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
51 views

Why does SQL Server 2005 only use an IS lock in this scenario?

I am attempting to examine the locking behind different queries but with the same output to avoid deadlocking situations. I have the following code: SELECT MIN(ID), TextData FROM PKKEYTESTER ...
2
votes
0answers
43 views

SQL Server 2005 Datawarehouse DB - Integrity failure caused by NonClustered indexes

Last year I've been working with a data warehouse database (SQL Server 2005) loaded on a daily basis during the nights. Overall database performance is acceptable. However, there is a "known error" ...
2
votes
0answers
182 views

How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types

We are trying to figure out root cause of slow running sql server queries hitting/fetching data from one of the database , size 300 GB, hosted on server with below configuration: Windows server ...
2
votes
0answers
92 views

Proxy Account SSIS and NETWORK SERVICE

I currently have an instance of SQL running as NETWORK SERVICE which for various reasons I can't change. I have a maintenance plan running under a proxy account which has access to a network share to ...
2
votes
0answers
40 views

Can I convert SQL Server 2008 full db to SQL Server 2005?

Recently I converted my SQL Server 2005 database to SQL Server 2008. Now I can't attach that db to SQL Server 2005. I do not have any backup of that db file as well. I have tried to generate scripts ...
2
votes
0answers
89 views

Quirk update for cumulative sum of chances for a raffle. What can go wrong?

I need to make a raffle where users have different chances. | USER | CHANCES | |------|---------| | A | 10 | | B | 25 | | Z | 15 | Looking in this answer: drawing a riffle ...
2
votes
0answers
532 views

DBCC CHECKDB reports torn page. How do I find what is in the torn page?

Using SQL Server 2005 I get the following error when running DBCC CHECKDB SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual ...
2
votes
0answers
144 views

SQL Server Database Mail Log is flooded with errors (20-30 per second) when mail server goes down

We're using SQL Server 2005. Every so often our mail server goes down, and when this happens, as soon as our SQL code tries to send dbmail, the Database Mail Log has an item which looks like this: ...
2
votes
0answers
137 views

backup history script produces incorrect results

I have a database that is backed up via maintenance plan. The database is scheduled to be backed up at 8:00PM. It usually takes 6 hours to complete. According to this query, SELECT distinct ...
2
votes
0answers
729 views

TempDB MDF file huge after update script ran

On the weekend we had a huge deployment run that affected a large chunk of our system. It was all run in one massive transaction (probably the wrong thing to do). As a result our TempDB DatabaseFile ...
2
votes
0answers
123 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
59 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
158 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 ...
2
votes
0answers
163 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
644 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
227 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
70 views

SQL Server 2005 Cluster Service pack 4 upgrade failure at remote user account

I built SQL Server 2005 ENT Cluster on Windows Server 2008 R2. I need to upgrade it to SQL Server 2005 SP4. However, I get this error at the Remote User Account screen Enter the remote user ...
1
vote
0answers
66 views

Replication slowness: massive MSrepl_commands table?

Background: I have a SQL Server 2005 transactional replication setup that pushes normal traffic fine, but occasional larger processing batches have been a problem. Obviously the changes take longer ...
1
vote
0answers
44 views

How do I ensure that bulk inserts must fire my DML trigger?

I have a table using a DML trigger to ensure data integrity. Although authors of SSIS packages, BULK INSERT queries, and bcp executions can enable DML trigger execution, which is disabled by default, ...
1
vote
0answers
243 views

SQL SERVER 2005 slow Performance on Windows Server 2008

Recently I have upgraded my server to new one. But because of compatibility issues with my old Windows Server 2003 R2 32 bit, I have also upgraded it to Windows Server 2008 R2 64 bit. (I have ...
1
vote
0answers
99 views

Can you speed up this query to aggregate millions of rows?

We store our website traffic data in SQL Server 2005, like so: | Date | IPAddress | GalleryUserID | ThumbView | LargeView | ZoomView | | 2014-11-11 | 192.168.0.1 | 22 | true ...
1
vote
0answers
97 views

MDF/TempDB Growth, SQL Server 2005 Enterprise

Here's my scenario: SQL Server 2005 Enterprise Records from Database A being added to Database B Size of Database A MDF file: 3.15 GB I have separate drives for Data, Logs, and System. I ran a ...
1
vote
0answers
83 views

SQL Server 2005 CHECKCATALOG fails

CHECKCATALOG is failing on a database that originated in SQL Server 2000. I am getting errors of the type: Msg 3853, Level 16, State 1, Line 1 Attribute (object_id=489156888) of row ...
1
vote
0answers
49 views

Flush procedure cache or drop and create?

Okay I had a very strange issue today on SQL Server 2005. We had a stored procedure update that added new parameters to part of it. One database out of ~150 or more seem to not work properly. Using ...
1
vote
0answers
244 views

Inside stored procedure, set column names of returned query according to variable

I have a query which I'd like to reuse, instead of having many different copies of it just for the sake of changing the column name. The report (for example) uses the same query, but there are either ...
1
vote
0answers
57 views

Will installing WebMatrix (and Consequently SQL Server CE) Break My Current Installation of SQL Server 2005?

We have a server that has (and needs to keep) SQL Server 2005. We are hosting intranet sites on one server (server 1), but have a database on another server (server 2). One of these sites uses AJAX ...
1
vote
0answers
85 views

In SQL Server sp_cursorprepare/sp_cursorexecute run extremely slowly

I'm running an application (Pentaho user console with the Saiku Ad-hoc plugin) which creates a query and runs it using sp_cursorprepare & sp_cursorexecute standard stored procedures. My model is ...
1
vote
0answers
57 views

Integration Service cannot be upgraded/removed

I have Sql Server 2005 Integration Services installed in our server whose version is 5292. It needs to be upgraded to version 5324. After running the patch the summary report says it is upgraded ...
1
vote
0answers
197 views

Backup of msdb stuck in rollback

A native full backup of msdb on a SQL Server 2005 Enterprise instance (9.0.4053) deployed on a two-node Win2K3 cluster (Microsoft Windows NT 5.2 (3790)) is currently stuck in rollback. The DB is in ...
1
vote
0answers
171 views

Way to make differential backup from full different but from same database

I have the following situation: Our sql server is running in a cloud service. The database to be backed up have 60gb. We need to make a local copy of the database for some tests, and it need to be up ...
1
vote
0answers
485 views

No SQL Server instances show in upgrade setup

I'm trying to upgrade SQL Server 2005 sp4 to SQL Server 2008 R2. However on the upgrade wizard doesn't show me any instances to upgrade. Any ideas? The instance is there just fine. I can connect ...
1
vote
0answers
103 views

Backup headerinformation hold wrong database name

We're in the proces of preparing our migration to our new servers aswell as SQL 2012 and everything is looking fine and dandy and i'm testing some of the scripts we will be using. One of these ...
1
vote
0answers
371 views

Problem with sorting matrix groups in SSRS

I've hit the following issue in SQL Server 2005 Reporting Services This is a slightly odd and complicated situation: I have a table containing various groups and within each group is also embedded a ...
1
vote
0answers
3k 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 ...
1
vote
0answers
253 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
191 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
78 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
139 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
333 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
546 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
18 views

Trigger copies history table: Will removing index lock options speed up the process?

I have a trigger applied to a table to write inserts and updates to a history table. That history table can grow pretty fast, and we want to see, if we can speed up insert operation a bit. The values ...
0
votes
0answers
75 views

DatabaseMail didn't start when an email is queued

This is about SQL Server 2005. I have configured SQL agent jobs to send an email when it failed. So far I have received email reliably. However, I have an weird problem where an email would get ...
0
votes
0answers
14 views

A transaction has been utilizing the version store for X minutes

For one of our servers we are getting below critical alert from Idera Diagnostic manager saying: A transaction has been utilizing the version store for 93 minutes on ABCKP1. The current version ...
0
votes
0answers
190 views

Supplied parameter sid is in use when creating a Login in Sql server

I am trying to transfer the logins from the production instance to the test instance after a refresh activity for restoring the DB's regarding the same. From the output of sp_help_revlogin on prod ...
0
votes
0answers
49 views

SQL query running slow compared to its earlier execution time

One of our application using an SQL query is reported running slow. Current execution time:- 15 minutes and so Earlier execution time:- 30 seconds. SQL-Sentry Plan for the slow query I've ...
0
votes
0answers
91 views

Sql agent job fails with access denied error

We are trying to execute a SQL agent job which executes a step to shut down a service on DEV ENV using CMDexec, but it fails with error below: Executed as user: ABC\mssql. System error 5 has ...
0
votes
0answers
22 views

Restore databases from prod to test server with data over say X days in SQL Server 2005

We've got a list from app owners to restore say 20 databases from a total of 100 available production server databases onto a test server on weekly basis. Moreover, they require it in a way that ...