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

learn more… | top users | synonyms (2)

2
votes
2answers
199 views

Is it possable to do a ALL SERVER DML Trigger?

My department works on converting a client's database when a client switches from another company to us. We have a RDP session they can connect to and see our software with their data. Once they ...
0
votes
0answers
56 views

SQL Server 2005 Create a SQL Server failover cluster check button is not enabled

I try to install SQL Server failover cluster under windows 2003 or windows 2008. In both installing time I found the checkbox enable error. I don't know any changes want to do to windows server before ...
2
votes
1answer
205 views

Double queue reader or is it still busy?

I came across this line in the verbose log on replication QueueReader Agent job startup. The Queue Reader Agent has encountered the error 'Another queuereader agent for the subscription or ...
6
votes
1answer
239 views

Failed copy job deletes all users

Since the progression of this was apparently somewhat difficult to follow the first time around: I attempted a completely boring, been-done-a-thousand-times-before copy of a database using the copy ...
2
votes
1answer
922 views

Cannot rename the table because it is published for replication

I need to rename some tables in my DB - SQL Server 2005, with this: EXEC sp_rename 'dbo.TableName', 'Tmp_TableName', 'OBJECT' but I can't do this - I get this exception - Cannot rename the ...
0
votes
1answer
36 views

Joining 2 tables- in Select statement show how many fields matched directly (A1 = B1), vs matched on isnull(A1,B1) = B1

I have 2 tables which I need to join, but sometimes there are 1) direct joins e.g. t1.FieldA = t2.FieldA OR 2) when t2.FieldA is null then it must join as well then t2.FieldA is null t1.FieldA = ...
2
votes
1answer
220 views

SQL Server Linked Server error and Query Problem

I am having two servers running SQL2000 and SQL2005. There is a database A,B in SQL2000. Similarly i have the same in A,B in SQL2005. A,B are name of the database. I have a linked server pointing ...
2
votes
2answers
535 views

SQL Server Replication Agent Profile -Output Parameter

I'm trying to add the -Output parameter to my log reader agent for transactional replication and getting this error: Msg 21805, Level 16, State 1, Procedure sp_MSvalidate_agent_parameter, Line 92 The ...
5
votes
3answers
30k views

SELECT DISTINCT on one column, while returning the other columns?

I have a query which uses three lookup tables to get all the information I need. I need to have DISTINCT values for one column, however I also need the rest of the data associated with it. My SQL ...
0
votes
1answer
42 views

Linked SQL Servers

I'm trying to link 2 SQL Servers (2008 and 2005). I have tried it via the GUI and scripts, and having no luck. I have problems trying to to query through it with access. As I'm not the Admin of the ...
0
votes
0answers
267 views

sql serer 2005 installation windows 8 64 bit

sql serer 2005 installation problem in windows 8 64 bit The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log ...
0
votes
0answers
336 views

SQL Server: Batch Requests/sec showed increase in performance, but performance actually decreased

We are currently on SQL Server 2005, with a plan to move to SQL 2012. To make this move we have to test new database drivers for the application (Java). We performed 2 load tests using the 2 different ...
-2
votes
2answers
131 views

SQL Server Transaction logs [closed]

Could someone tell me the role of transaction logs in SQL Server ? I have a transaction log file, after making a lot of transactions for testing purpose I take the backup of it via command. Sometimes ...
1
vote
1answer
119 views

User switching accounts, can't log into MS SQL Server anymore [closed]

First a bit of background, for good or bad, we don't keep one user account attached to one person. The user accounts are matched to their position. For instance the CEO would have a userid of CEO. ...
3
votes
1answer
71 views

Advice on setting up a Reporting server

I'd like to ask advice on setting up a reporting server please. We have a SQL Server (on a VM) that handles operations. (SQLOps) This primary SQL server instance has a secondary Log Shipping ...
0
votes
2answers
89 views

SQL Server 2005 Replication Stops after a minute without an error

Background: I have a SQL Server 2005 setup with master, slave1, slave2 replication set up as a pull replication from slaves. The distribution database resides on the slave1 machine, both slaves pull. ...
5
votes
1answer
3k views

SQL Server has encountered 1 occurrence(s) of cachestore flush

On one of our production server log files, the following message is being observed on daily basis. What does it mean? Is it a serious issue? SQL Server has encountered 1 occurrence(s) of ...
3
votes
1answer
93 views

DBCC show_statistics: density doesn't match what I calculate

I've read in several articles that the density of a column is (1.0 / count(distinct columnname)). I have a column with roughly 2.6 million distinct values over 2.7 million rows, but the column ...
1
vote
3answers
247 views

How to determine if SQL Server will not execute parts of the script in parallel?

Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"? USE UserDB; GO DBCC SHRINKFILE (DataFile1, 5); GO DBCC SHRINKFILE (DataFile2, ...
2
votes
2answers
140 views

Delete corrupt empty filegroup

We have a large database (1.8TB) with eight filegroups created on SQL 2000 and now running on SQL Server 2005 9.0.5057 since early 2012. Simple recovery. On September 27, we got a torn page error in ...
1
vote
2answers
4k views

SQL Server 2005 Express on Windows 8

I need to install SQL Server 2005 Express on Windows 8 Home Edition x64. But I get a compatibility error! I have tried SP1, SP2, SP3 of SQL Server 2005. Visual Studio 2008 is working fine. Any ...
6
votes
4answers
41k views

Combine column from multiple rows into single row

I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
3
votes
2answers
126 views

Query has where (a = a) or (b = b) - the 2nd part causing index scan. Full example included

I've been trying to figure out why this query (on integers) is using an index scan when both values (the original table, and the table its looking up in) are integers. The first part is used to fill ...
1
vote
1answer
650 views

How to insert in table from remote stored procedure without creating a distributed transaction?

How can I insert in a local table from a remote stored procedure without creating a distributed transaction? I want to run a stored procedure on a remote server and use the output as the source for an ...
3
votes
1answer
267 views

Selecting the highest value in a column?

I am dealing with a table that has 3 columns and I need a way to select the record(s) that matches a certain PrntP_PstnGrpID(s) and is the highest ChldLevel ( The PrntP_PstnGrpID could have multiple ...
4
votes
1answer
318 views

After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB

I have a table: OrderDetails. For 5 years, only the odd Deadlock has occured. If it happened, it happened on indexes which was on OrderDetails table Now all of a sudden, deadlocks are everywhere - ...
0
votes
1answer
253 views

dbcc checktable/checkdatabase errors after database restore

Following on from this question Error when creating an index in SQL Server 2012 we have been having problems restoring our live database back to test. Our current production database is SQL 2005 ...
10
votes
4answers
2k views

Table partitioning for archiving data

Scenario: two databases: DB_A and DB_Archive with one very big table called tableA. every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing ...
1
vote
0answers
42 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
1answer
348 views

Retrieving data speed tweaks SQL Server 2005

I have a database in Microsoft SQL Server 2005. I have table with 3 columns, namely HASHKEY (BIGINT), NOTE_ID (INT) and TIME_OFFSET (INT). Columns HASHKEY has 19 digit BIGINT values, which can ...
1
vote
3answers
2k views

How do I determine if a database is the principal in a mirroring setup?

I have two database servers Server1 and Server2, configured with mirroring. A single database, MirrorDB, is mirrored. There is another database on Server1 named OtherDB which is only present on ...
1
vote
0answers
158 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 ...
0
votes
1answer
98 views

Need to create combinations of Parents/Children/Dogs, but can't get past 2nd level of CTE

I've hit a brick wall and please ask help. I'm building a table up for (in this example) a ticketing system where e.g. Ticket 1 will allow Parent: Either parent Peter or Sally, Child: to come without ...
1
vote
1answer
74 views

Creating domain user login from another domain?

I have two domains named "Domain1" and "Domain2". I have SQL server 2005 in Domain2, and users in Domain1. How can I create a login in SQL Server 2005 (in Domain2) for the user that is present in ...
11
votes
3answers
5k views

Is it possible to restore sql-server bak and shrink the log at the same time?

We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it ...
7
votes
2answers
2k 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, ...
1
vote
1answer
1k 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 ...
2
votes
1answer
571 views

TempDB on Solid State Drives on SQL Server Cluster

Can solid state drives be used for the TempDB on a SQL Server failover cluster if they are local drive on both machines? It is supported in 2012 but our cluster is a SQL 2005. The application that it ...
3
votes
2answers
357 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 ...
1
vote
0answers
97 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
207 views

bulk insert not working in stored proc when using exec [closed]

We have a SQL Server 2005 database with a stored procedure that needs to do a bulk insert. The query in the stored procedure is defined as such: declare @query nvarchar(max) set @query = N'bulk ...
11
votes
3answers
2k views

Can I move rows between partitions by updating the partition key?

I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this. The question: Can you move rows of data within a partitioned table from ...
0
votes
2answers
307 views

how to create backup for update tables structures in existing DB?

I have Sql Server DB, I know I can Create thru "Generate Scripts" backup for SP and Functions then I just replace inside the script the "CREATE" with "ALTER", that will update the target DB with the ...
1
vote
1answer
76 views

how to deal with a SSIS 2005 validation error in a script component

Within a script component, a strange error occurs. the (vb.net) code leads to build succeeded as mentioned, build done BUT and that's not really awesome (and, yes, design script was clicked): ...
6
votes
3answers
526 views

How do I create a user for SQL Server whose queries (any/all) are time limited?

Is there any way to create a database user in SQL Server 2005 so that none of their queries are allowed to exceed a certain time limit (for e.g 20-30 seconds) or all queries are limited to a certain ...
1
vote
0answers
1k 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 ...
4
votes
1answer
494 views

Failover scenario with principal “hard down”

My company has a failover pair of SQL Server 2005 instances, which provide database availability for a UL-licensed alarm/call center, where uptime and availability are critical for life safety. ...
1
vote
1answer
244 views

Mysterious merge replication problems creating a subscription

We have a merge publication which had been mostly working OK for a long time (years). Recently an article was added, we were informed the snapshot was out of date, so we regenerated it. Then we got ...
0
votes
0answers
34 views

Restoring Master database SQL Server 2005 [duplicate]

Because of sudden power-cut my server has gone down and the master database was corrupted, so I had an old version of (master.mdf) which I copied and pasted to folder C:\Program Files\Microsoft SQL ...
0
votes
0answers
320 views

Cannot recover the master database. SQL Server is unable to run

Right, I powered off development server because it was hanged, when restarted directory services database was corrupted, so fixed it, then figured out that IIS Manager's metadata.xml is messed up, so ...