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

learn more… | top users | synonyms (2)

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
29 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
708 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
130 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
65 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
30 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
44 views

INSERT from SELECT using a CURSOR

I have a table that looks like this: +====================================================+ | aid | uid | eid | name | dob | paid | ...
0
votes
1answer
187 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
755 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
463 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
727 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
53 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
727 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
279 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: ...
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
339 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
153 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
98 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
36 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) ...
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
999 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
21k 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
36 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
121 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
108 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
71 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
436 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
301 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
75 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
299 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 ...
7
votes
1answer
1k views

Non-yielding IOCP Listener

Does anyone know what `Non-yeilding IOCP Listener' indicates? On of our SQL Servers just had a bugcheck dump: ===================================================================== ...
2
votes
0answers
79 views

No significant performance increase after migrating from SQL Server 2005 32bit 4 Cores 4 GB RAM to SQL Server 2012 64bit 4 Cores 32 GB RAM [closed]

We run regularly a series of data analysis scripts on one of our databases. The MDF File of the database is several gigabytes big, the scripts perform not only selects, but also inserts, updates and ...
4
votes
2answers
5k views

Can I copy the table structure of a #temp table to a new physical table?

I have a #tempTable that was created using SELECT * INTO #tempTable FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls', 'SELECT * FROM [Sheet1$]') Is there an easy way ...
2
votes
1answer
146 views

Get employee names on the basis of Date

I want a dynamic query which will fetch the employee name who has completed successfully 6 months and are ready for confirmation. I have written a hardcode query does that. Here is the query SELECT ...
1
vote
2answers
155 views

Role to view SQL server job log

We have a monitoring tool that can check if Microsoft SQL Server jobs have run successfully. What I want to do is create a user that has as little permission as possible but it must be able to watch ...
0
votes
2answers
638 views

SQL Server 2005 to 2012 Replication, cannot access replicated data

I have created publication (snapshot, small table with 10 rows) on MS SQL Server 2005. Then, I created push subscription on MS SQL Server 2012. When I try to select data form table, process takes ...
2
votes
4answers
2k views

SQl Server Password Change after installation

I have installed SQL Server 2005, but at the time of installation I forgot to change my password. Now I can access my database only in Windows authentication mode, and I want to access the database in ...