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

learn more… | top users | synonyms (2)

5
votes
4answers
1k views

Create a secondary read-only copy of DB in a different server

We have users that query our prod database (mostly ODBC through MS-Access) to create some customs reports. We want to copy our data to a second server (replicate / log shipping / ???) so the load on ...
1
vote
0answers
173 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 ...
4
votes
2answers
12k views

How to break SQL script execution

I am working on sql script and I am having a requirement of stop continuing the script if some conditions are not satisfied. When I Google it, I found the RaisError with 20 severity level will ...
1
vote
1answer
182 views

How big should I make a column in an audit table for storing the output of COLUMNS_UPDATED()?

I have a stored procedure which creates a complementary trigger and audit table when a table name is passed into it. The SP filters out all the calculated columns and other stuff that stops you doing ...
2
votes
1answer
426 views

DBCC CHECKDB ERROR Msg 8992

I have got the Following Error When i ran the checkDB over the database. What are the steps can be taken to resolve the issue. Without affecting the users. Msg 8992, Level 16, State 1, Line 2 Check ...
7
votes
2answers
4k views

Database size - MDF too large?

I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery ...
7
votes
1answer
10k views

Moving from SQL 2005 [SQL_Latin1_General_CP1_CI_AS] to 2008 - will I lose any features by using 'backwards compatibility'

We're moving from SQL 2005 [Instance and DB have collation of SQL_Latin1_General_CP1_CI_AS] to SQL 2008 [which defaults to Latin1_General_CI_AS]. I completed a SQL 2008 R2 installation, and used ...
1
vote
0answers
596 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 ...
3
votes
1answer
2k views

PAGELOCK Conversion Deadlock From Update to Intent Exclusive

A customer of ours is experiencing frequent deadlocks. The deadlocks are mostly on the same UPDATE statement. The deadlocks follow a pattern in which both SPIDs have acquired an update (U) lock on a ...
5
votes
1answer
88 views

Transfer logins to another server

I moved a database from SQL Server 2005 to SQL Server 2012. Is there any way of restoring all the logins on the new server?
5
votes
1answer
2k views

SHRINKFILE best practices and experience [duplicate]

Preamble: In general it's a big no-no, but believe me that are rare cases when space is really needed. For example Express Edition is limited to 10GB. Imagine that you discovered that with a data type ...
2
votes
1answer
833 views

How to update a SET statement so I can use the MIN() function?

I have an existing UPDATE table SET statement that was working until one of the Drivers used two different trucks in his/her shift, so now what I need to do is modify the UPDATE statement so I can use ...
3
votes
1answer
2k 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 ...
3
votes
2answers
507 views

Workaround for Anti-Semi Join bug

I built the following SQL Server query, but it is encountering the anti-semi join defect in SQL Server 2005 which results in inaccurate cardinality estimates (1 -- urgh!) and runs forever. Since it is ...
1
vote
2answers
4k views

How to make SQL Server Management Studio use latest version of SQL Server?

I'm upgrading my dev machine from Windows 7 to 8. While installing earlier versions of Visual Studio it installed SQL Server 2005. I then went to this site and downloaded and installed SQL Server ...
4
votes
1answer
3k views

sys.fn_varbintohexstr returns NULL if varbinary data is more then 2000 as datalength

sys.fn_varbintohexstr works fine if VARBINARY data length is upto 2000. If it is greater then 2000, function sys.fn_varbintohexstr returns NULL as result. What I am missing ? DECLARE @testvarbinary ...
3
votes
2answers
3k views

Should I refresh query plan cache

Please let me explain my problem and situation: I have a web application - MVC3, MSSQL Server 2005, LinqToSQL. It has been running great until one fine morning I pushed a lot of rows to a table that ...
2
votes
1answer
551 views

When changing startup parameters on a cluster, does each node have to be updated manually?

When moving a SQL 2005 instance to new disks on a two-node Windows 2003 cluster, I changed startup params on the active node to reflect the new paths for the master DB files and error logs. I ...
2
votes
1answer
3k 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
568 views

Does openquery use distributed transactions?

Does openquery in SQL Server 2005 use distributed transactions? If so, in which cases does it?
1
vote
0answers
105 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 ...
0
votes
1answer
301 views

UPDATE statment with and without SELECT keyword

I have been doing some testing on removing the SELECT keyword from this update: Update #tmpDriverTime Set WorkDay2 = (Select IsNull(Sum(PyblHrs),0) From spr_DriverTimeEntry Where ...
1
vote
2answers
220 views

SQL Server 2005 onwards- Capturing Baseline

I want to design a process by which I can capture the baseline data of multiple SQL Server instances (2005 onwards) in a single database from where I can get all the info and alerts. As of now I ...
11
votes
2answers
20k views

Which one is more efficient: select from linked server or insert into linked server?

Suppose I have to export data from one server to another (through linked servers). Which statement will be more efficient? Executing in source server: INSERT INTO ...
2
votes
2answers
270 views

How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped. The table hierarchy is as follows: Order table contains OrderReference(PO), OrderID ...
0
votes
0answers
26 views

How to join multiple tables (SQL) to find the difference between an order and shipped quantities? [duplicate]

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped. The table hierarchy is as follows: Order table contains OrderReference(PO), OrderID ...
6
votes
2answers
643 views

Query Plans not retained insufficient memory errors

We have been experiencing memory issues with SQL Server. We first realised we had a problem when we started getting timeouts and login errors: A connection was successfully established with the ...
2
votes
2answers
832 views

Improve CTE performance

is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER? DECLARE @File_Name VARCHAR(8000), @Disk ...
6
votes
2answers
2k views

TempDB Version Store cleanup

From what I can find the version store will only clean up versions that are older than the oldest active transaction. Question: Is the oldest transaction database specific or will SQL Server keep all ...
1
vote
1answer
518 views

How to identify who made changes in SQL server management? [duplicate]

I'm working on the server using SQL Server Management Studio 2005. Is it possible to identify the person who made changes in the SQL server Management Studio 2005 tables data?
5
votes
2answers
391 views

How to design index for faster deletes

I want to speed up following piece of code delete from ssn_sdo where (art_id=@art_id and skl_id=@skl_id and @level=0 ) or (art_id=@art_id and skl_id=@skl_id and @level=1 and ...
-4
votes
0answers
92 views

Multi-row INSERT rejected? [closed]

What's wrong with my query? :( USE tempdb; IF OBJECT_ID('dbo.OrderTotals', 'V') IS NOT NULL DROP VIEW dbo.OrderTotals; IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails; ...
0
votes
1answer
2k views

Error 10053 connecting with Management Studio

While I try to connect to SQL Server, I am getting the following error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: ...
3
votes
3answers
2k views

SQL Server freezes (because of application), need logging

We have an application running ontop of our SQL Server 2005 instance and a couple of times a week this application (unannounced) will cause the SQL Server to freeze. I can't even restart the SQL ...
1
vote
1answer
435 views

Migrate database from SQL Server 2005 to SQL Server 2008 R2

DBAs... please be gentle with me, I'm a 'greenhorn' SQL Server DBA, pressed into service because my company's DBA had resigned and we haven't found his replacement, yet. First, my situation: I'm ...
4
votes
1answer
887 views

Forcing Join Order

I have a table with just over a million rows. These rows can have a parent record in the same table which is found by joining to itself on 6 different columns (i.e. there is no single ParentID ...
2
votes
1answer
123 views

Powershell SQL Script Execution in Selected Schemas Only

I have many schemas in one database. In that database I have some SQL scripts to be executed. I want to execute those SQL scripts in only selected schemas. Suppose I have 10 schemas in total (A to ...
-2
votes
1answer
100 views

Select data one by one from table and data need to repeat custom number of times [closed]

I have table data like data1 data2 data3 data4 and I need to select these rows like this: data1 data1 data1 data1 data2 data2 data2 data3 data3 and how to perform this in SQL Server 2005?
2
votes
2answers
168 views

PowerShell to get database schemas

I am able to connect to a remote SQL Server with the instance name using PowerShell. I am using an XML file (something like a configuration file) which includes all my database details. PowerShell ...
3
votes
2answers
137 views

How to determine that the chain of log degraded

SQL Server 2005. I have DB with full recovery model. If i execute on my base ALTER DATABASE test SET RECOVERY SIMPLE ALTER DATABASE test SET RECOVERY FULL or BACKUP LOG test WITH truncate_only ...
5
votes
1answer
4k views

Need to divide single column into multiple columns

My table has data with a single column: 1 2 3 4 5 6 7 8 9 10 And I want output like this: 1 2 3 4 5-first row 6 7 8 9 10 - second row Another has two columns like this: Column 1 ...
6
votes
1answer
2k views

How do these snapshot isolation level configurations interact on a SQL Server 2005 instance?

I started troubleshooting a tempdb issue we are having on SQL Server 2005 Enterprise Edition. A developer is getting an error that tempdb is out of space. Technically, the error is: Transaction ...
0
votes
2answers
450 views

Shrink transaction log while transaction in progress

I have a several-GB import into my SQL Server 2005 (Standard Edition) database. The import is separated into 4 parts, each part in its own transaction. As you can guess, the transaction log files ...
0
votes
3answers
93 views

Access to tables in SQL Server

I would like to know if there is a table where accesses to tables are saved, per user or per system. I'm using both SQL Server 2005 and 2008.
3
votes
1answer
562 views

Does the week day number changes according to localization in SQL Server?

Does the weekday number changes according to server/database regional configuration in SQL Server (2005+)?
4
votes
1answer
487 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
2answers
357 views

History from SQL Agent Jobs (SQL Server 2005)

how can I able to see all available records from SQL Agent Jobs, because in SSMS is dispayed only reduced number of records are these records stored in system databases, not only in plain log files ...
0
votes
1answer
1k views

How to disable SSMS table designer timeout?

How do i disable the Table Designer timeout in SQL Server Management Studio? Background This KB Article seems to indicate that if you stop overriding the Designer timeout: To resolve this ...
2
votes
0answers
739 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 ...
1
vote
0answers
405 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 ...