SQL Server 2005 (major build version 9.00.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (2)

3
votes
1answer
3k views

What are the most compelling reasons to upgrade from SQL Server 2005 to SQL Server 2012?

We currently have 2 SQL Server 2005 Enterprise machines in a mirrored environment running on Dell R815 servers. These machines consist of: Four AMD Opteron 6176 Processors (12 cores at 2.3Ghz), for ...
26
votes
6answers
59k views

SQL Server Cannot drop database <dbname> because it is currently in use… but no sessions displayed

When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database. ...
1
vote
0answers
139 views

Merge Replication: Error “The insert failed. It conflicted with an identity range check constraint”

For one of our SQL server 2005 enterprise, configured with merge replication, we suddenly start seeing the below error message: The insert failed. It conflicted with an identity range check ...
2
votes
0answers
36 views

SQL Server not checking for expired passwords

We are using SQL Server 2005 on Windows Server 2008 with Windows Authentication where access is granted to members of a specific domain group. Clients are VB6/ADO applications that connect via an ...
2
votes
0answers
845 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 ...
3
votes
2answers
199 views

Is is possible to recover a database from the 'raw' files (.mdf and .ldf) of the old server to a new SQL Server instance on a new server?

I have a client that has POS software called Restaurant Pro Express (RPE) from www.pcamerica.com. RPE uses a SQL Server database. Their old POS server had a hardware failure, but I was able to attach ...
1
vote
0answers
31 views

SQL Server 2005 Database Mail Resends Historic Messages following SP4 Build 5324

I have applied SP4 Build 5324 to a SQL Server 2005 Enterprise active/passive cluster (Build pathway: 4053 to 5000 to 5324). The SQL Server log contains zero errors. The SQL Server Agent log contains ...
12
votes
4answers
4k 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 "...
0
votes
0answers
38 views

How to improve the slow performance with Insert operations in SQL Server 2005?

Suddenly our server started to have a bad performance, after trace the error, I identified that the inserts are the problem, this seems a know bug: https://support.microsoft.com/en-us/kb/940545 (The ...
1
vote
1answer
30 views

Can I enforce one field being dependent on another?

I would like to know if it's possible to have two fields (in this case bits) in the same table where one is dependent on another. For example Check2 may only be true if Check1 is true. Note: I'm ...
1
vote
1answer
720 views

Cannot shrink transaction log [duplicate]

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 ...
0
votes
0answers
134 views

Reorganize index maintenance plan failed

A 'Reorganize index task' (maintenance plan) failed today with the below error: Reorganize index task The Execute method on the task returned error code 0x80004003 (Object reference not set ...
2
votes
0answers
66 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
6k views

SQL Server - backup / restore schema only

We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - ...
0
votes
1answer
32 views

Backup Principle database in SQL Server

We have set up database mirroring in SQL Server 2005. Is it possible to take full backup of principle database? Does it break database mirroring if I will take full backup of the principle database?
0
votes
1answer
45 views

INSERT from SELECT using a CURSOR

I have a table that looks like this: +====================================================+ | aid | uid | eid | name | dob | paid | +====================================================...
0
votes
1answer
190 views

Import (flat file) into SQL Server

I've seen a couple of questions about the subject backfiring so I'll try to be specific about my concern... I have an old (MS Access) frontend I'm trying to rebuild and I left the part that troubles ...
5
votes
2answers
774 views

BCP stops after the last “1000 rows sent to SQL Server. Total sent: …”

I ran SQL Server 2005's bulk copy program BCP like this: bcp mydb.dbo.mytbl in myfile.blk -c -S mysvr -U mylogin -P mypass And it ran and produced output like so: Starting copy... 1000 rows sent ...
6
votes
1answer
2k views

How to restore replication after server crash

We had two databases on one SQL Server 2005 instance with transactional replication between them (3 tables as articles). This server acted both as publisher and distributor. Then the RAID on the ...
1
vote
2answers
476 views

Bi-directional synchronization between local SQL Server 2005 and a SQL Azure 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 ...
8
votes
1answer
738 views

Why is the page life expectancy 0 (zero) on SQL Server 2005

We have been wanting to know the page life expectancy for our SQL Server. So we look at the counter using Perform. The value is ZERO and never changes. I thought there must be some bug, so I checked ...
0
votes
1answer
55 views

How to Create Trigger Insert with a SELECT LEFT OUTER JOIN

I want this trigger to be fired after an insert is made with the text, this is what I have so far ALTER TRIGGER [xxx] ON [dbo].[Main] AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT ...
2
votes
1answer
84 views

Single row update using primary key affects many rows

I'm doing a very simple datetime update in a table using its primary key: update TABLE set END_DATE = '2015-10-14 00:00:00' where ID = 1165027; I received the message (1 row(s) affected) 183 ...
0
votes
4answers
1k views

Why Clustered Index property is enabled on few columns and not on others?

I heard that there can be only one Clustered Index? I may be wrong, but I am sure I read somewhere. In one of my SQL Server 2005 table, I have following columns: SerialNum ------- BigInt -------- ...
1
vote
3answers
767 views

SSMS 2014 working with SQL Server 2005 instances

Is there any major complication that one could run into while using the latest (developer) version of SSMS with older SQL Server 2005 instances? Any way one could affect the data by using this ...
4
votes
1answer
295 views

NUMA Nodes - MAXDOP - PLE

We have a server with 8 CPUS across 2 NUMAs with hyperthreading enabled. Currently Maxdop is set to 8, but actually should be set to 4 as per the Maxdop section of this article: https://support....
29
votes
5answers
8k views

Downgrading from SQL Server 2008 to 2005

Database files that are built using SQL 2008 are not compatable with 2005. Is there a work around?
5
votes
2answers
347 views

SQL Server log file used pecentage does not drop after multiple backups

SQL Server 2005 I ran "dbcc SQLPerf(logspace)", then took two log backups, and ran "dbcc SQLPerf(logspace)" again. No change from 48.55% used, even though "dbcc opentran (foo)" says that there are no ...
3
votes
1answer
155 views

Enabling Constraint Silently Fails

Why would SQL Server silently fail to re-enable a foreign key constraint? I recently attempted to re-enable a number of constraints using ALTER TABLE. Most enabled successfully; however, minority of ...
-5
votes
1answer
100 views

Finding users in the database owner role [duplicate]

I need a script which will list out all the users with the database owner permission for a specific database.
0
votes
1answer
39 views

all rows from left table with sum of amount from right table

I'm trying to return a list of customers and the sum of their transactions in the last week, but have the query return customers who didn't wager at all. I am trying to do this with a left join, but ...
6
votes
1answer
1k views

adding an order by to this query returns faster than without, why?

Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen? SELECT TOP (500) r.cID,r.aField,a....
0
votes
1answer
38 views

SQL server data copy bug? [closed]

I have one query who shows some data, and those data I'm manually copying to Table1. The query shows the numeric type and it is in this form Query +---------------+ | status ...
3
votes
3answers
1k views

Restoring SQL Server databases - tips and tricks?

Anyone have any tips and tricks when it comes to restoring SQL Server databases? I recently restored a .bak from one server/instance to another, using SQL Server 2005 on both, and have run into the ...
9
votes
2answers
22k views

SQL Server backup failing. Error: 3041, Severity: 16, State: 1

First of all, I'm very new at stackexchange, so please bear with me. I'm running a SQL Server 9.0.4060. My problem is: my SQL Server backup keeps failing on a lot of the databases. The Maintenance ...
2
votes
0answers
39 views

Join duplicates on T-sql [closed]

Background: This is largely a learning exercise. I am working on a task scheduling program. Part of the program requires making a report to print out. I am working with the original version of the ...
1
vote
1answer
59 views

microsoft sql server schema [closed]

From blog.sqlauthority.com: Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such ...
0
votes
0answers
38 views

Converting Legacy SQL Server 2005 Stored Procedure to Transaction based for parallelism [closed]

My task is to convert a legacy stored procedure written in SQL Server 2005 so that it can be used in parallel (multiple executions from different users). As of now the stored procedure is designed ...
2
votes
1answer
131 views

Tracking down unused Views and Stored Procedures

I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends. This database needs a lot of love and attention which I am attempting to give it. There are lots of stored ...
8
votes
2answers
5k views

Turning off auto shrink on all SQL Server databases. Why doesn't this work?

I figured I could use sp_MSforeachdb to solve this problem, but I get an error message. sp_MSforeachdb ' BEGIN USE [?] DECLARE @dbid INT SET @dbid = DB_ID() IF(@dbid > 4) BEGIN --PRINT ''[?]'' ...
7
votes
2answers
7k 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 ...
0
votes
0answers
131 views

SYNTAX ERROR: delete from multiple tables with inner join

Sorry, but I need syntax help :( This needs to run on SQL Server 2005 + DELETE SO, ASO, ASOV, AOV FROM SYS_OPTION SO INNER JOIN ADM_SYS_OPTION ASO on SO.OPTION_ID = ASO.OPTION_ID INNER JOIN ...
-2
votes
1answer
4k views

This version of Microsoft SQL Server Management Studio Express can only be used to connect to SQL Server 2000 and SQL Server 2005 servers

I have already SQL Server Management Studio 2008R2 on my local machine. I just installed SQL Server Management Studio 2005 and wanted to connect for the first time. After going through the following ...
1
vote
1answer
74 views

Import .bak into SQL Server 2008 Management Studio

I have a custom program that uses a database. I have little knowledge of servers but the way it works is by using a "local" server in the same computer as the one where the program is installed. I am ...
1
vote
2answers
471 views

Understanding the usage of version store in tempdb

We are trying to analyze the growth of tempdb on one of our SQL server 2005 Ent SP4. I know tempdb would need the space depending upon the transactions, and how queries are designed to use it. We ...
-3
votes
1answer
60 views

SQL Server questions (for masters) [closed]

How to rename sa to new_sa in SQL Server 2000? How to do and what is the impact SA rename impact in SQL Server 2005, 2008, 2008R2, 2012, 2014, Express Edition How to recreate sa login if deleted How ...
1
vote
1answer
332 views

Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060

I have a scenario where some inserts/updates are failing due to maximum row size of 8060 limitation. I have a table with lots of varchar 250 to 500 columns, numerics, datetime columns. I was trying to ...
4
votes
1answer
78 views

What started this SQL Server job?

I'm looking at a SQL Server environment in which jobs start in several different ways. One way is from a job running on a different SQL Server. So, for example, "SQL Server A" runs a job named "...
1
vote
1answer
1k views

SQL Server 2005: Integration Services Stored Packages is empty. Why?

I am trying to import, export SSIS packages. I know they are there. When I query msdb.dbo.sysdtspackages90 I get 22 packages. But when I connect to Integration Services on the server, the stored ...
3
votes
2answers
301 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...