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

learn more… | top users | synonyms (2)

8
votes
1answer
599 views

How to move a database from SQL Server 2012 to SQL Server 2005

What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)? I know I cannot: restore a backup of the database on SQL Server 2005 detach & attach I ...
1
vote
2answers
157 views

How can I run this SQL query across all of my databases at once?

I have a query to let me know basic info on backups. I have several SQL servers that I'd like to be able to query at once. Is it possible to run the following query across several servers at one time? ...
0
votes
1answer
902 views

Help with Transferring Linked Server to another server?

Please advice me as I'm trying to move the linked server (Providers with the tables in Catalogs) from SQL server 2005 to another upgraded sqlserver 2008, I scripted out everything from there, but not ...
0
votes
0answers
597 views

How to determine if SQL Server Reporting Services database is being used?

I have a SQL 2005 server that needs to be decommissioned. For regular databases, I have a couple scripts that look at sys.dm_db_index_usage_stats to determine last access. My normal process is to make ...
1
vote
1answer
494 views

Why isn't SQL Server using the Max Server Memory setting?

I have a SQL Server machine with the following: Windows 2003 Enterprise SP2 (32-Bit) SQL Server 2005 (32-Bit) 12GB RAM /PAE switch set in boot.ini 6 SQL instances, all with AWE enabled No other ...
1
vote
1answer
72 views

How to disable data type check while pushing data to Linked Server

I have two SQL serves one is SQL server 2005 and other SQL server 2008.I push data from SQL server 2005 to SQL server 2008 using Linked Server.I want to improve performance of data transfer. Few days ...
1
vote
1answer
101 views

SQL Server Login attempts fail when using SSMS but succeed when using SQLCMD

I have my SQLServer 2005 instance configured for mixed mode fine. When using SSMS, none of the sql server logins seem to work. Just "Login failed for user '..'" message. But when I use SQLCMD, it ...
2
votes
1answer
479 views

Performance issue with TOP (1) on a nullable column

I need help to identify the reason for a performance issue. Actually following query sits in a scalar-valued-function but that is not the reason for the issue since it needs the same time as query: ...
37
votes
3answers
857 views

Huge slowdown to SQL Server query on adding wildcard (or top)

I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not ...
0
votes
0answers
60 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 ...
0
votes
1answer
141 views

Shrink Database Did Not Reclaim Much Space

I have a SQL Server (2005 Developer Edition) database that has grown too large for it's drive. Yesterday evening I dropped a large table that was no longer needed and last night I set up a ...
0
votes
1answer
39 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
309 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 ...
0
votes
1answer
43 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
453 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 ...
1
vote
1answer
150 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
95 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 ...
1
vote
3answers
786 views

GUID stored in a varchar field

I have inherited several databases that use GUIDs as PKs. Instead of all the datatypes being uniqueidentifier most are varchar(50) and some varchar(100). Fields are true GUID some where created by ...
0
votes
2answers
149 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. ...
3
votes
1answer
105 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
400 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, ...
1
vote
2answers
5k 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 ...
3
votes
2answers
144 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 ...
0
votes
1answer
332 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 ...
1
vote
0answers
50 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 ...
2
votes
2answers
174 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 ...
0
votes
1answer
119 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 ...
0
votes
2answers
149 views

Bidireccional synchronization between a local Sql Server 2005 database and a SQL Azure database using Sql Data Sync

I need to synchronize an Sql Server 2005 database in an SQL Azure database. In other posts I've read that I can do this using Sql Data Sync but I don't know if SQL Data Sync can make the ...
6
votes
0answers
273 views

Dropped index from view still referenced in execution plans [closed]

On a SQL Server 2005 Enterprise edition server, an index has been dropped from a view. When running a select * query that includes this view, no results are shown. In addition, the execution plan ...
2
votes
1answer
780 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 ...
1
vote
1answer
104 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 ...
1
vote
0answers
133 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 ...
3
votes
1answer
465 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 ...
1
vote
0answers
224 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 ...
2
votes
1answer
304 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 ...
1
vote
1answer
123 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): ...
0
votes
1answer
634 views

Merge reports 'You must rerun snapshot because current snapshot files are obsolete', gets caught in Death Loop

We have a very large Merge publication for a data warehouse with some large (.bcp files on the order of 20GB) fact tables that is causing us tremendous trouble. Specifically, we created a publication ...
4
votes
1answer
770 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
413 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
2answers
351 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 ...
0
votes
0answers
489 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 ...
0
votes
1answer
200 views

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. ...
1
vote
3answers
6k views

Restoring .bak file SQL Server 2005 in SQL Server 2008

I don't have enough knowledge in SQL Server back and restore. I just want to know is it possible to restore an SQL Server 2005 .bak file in SQL Server 2008? If yes, how can I do it?
0
votes
0answers
41 views

SQL Server 2005 : Splitting Multiple Date Bands Into Individual Dates

How would I go about splitting multiple date bands into individual dates? So from something like this: DateStart 2013-01-01 2013-01-06 DateEnd 2013-01-03 2013-01-08 To something like this... ...
2
votes
1answer
476 views

SQL Server 2005 - Query optimization for fetching large number of rows from table with 750 million rows

Brief on application: This is audio fingerprinting application, being developed in Java with Microsoft SQL Server 2005 database. I have one application to create fingerprints of original songs and ...
0
votes
2answers
603 views

Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram?

I'm using Window Server 2003 Enterprise, Microsoft SQL Server 2005 Standard Edition. The problem is the SQL Server memory gets stuck at allocating 1.7GB RAM max even though I have 11GB of RAM left. ...
1
vote
1answer
418 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
1answer
585 views

Getting collation confict in IF statment and need help resolving it please

A SQL script I am writing is generating the error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_CI_AS" in the equal to operation. I ...
2
votes
1answer
4k views

No SA password. No SQL Server Management Studio. No OS authentication [duplicate]

I have this problem. I need to do some administrative tasks on a MS SQL Database using SA account. OS authentication is not set. SQL Server Manager Manager Studio is not installed. I have no other ...
1
vote
1answer
361 views

Cannot shrink transaction log

Bit of a headscratcher here. I have a db that has less than 1GB of data, but a 40GB log file. Transaction logs are backed up daily, and there is not a lot of activity on this database; approximately ...