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

learn more… | top users | synonyms (2)

1
vote
0answers
35 views

what if all the non clustered indexes on my table were filtered indexes?

I have a table that is highly hit on my database. this table has 370,370 records. when I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [...
0
votes
0answers
37 views

pros and cons of partition or not-partition this sql server table that is highly hit?

I have a table that is highly hit on my database. this table has 370,370 records. when I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [...
0
votes
1answer
36 views

Shrinking Database

We have two databases in SQL Server 2005 with installed navision database in both database. Due to disk space I want to shrink old database which is not in use and having 40% free space in .mdf and 20%...
1
vote
0answers
37 views

Under which conditions would selecting a small column off a primary key be slow?

Usually querying the table is in the millisecond range at most. But sometimes it takes 30 seconds and then times out. What could cause SELECT smallColumn FROM SomeTable WHERE pkID=@pkID to be slow? ...
0
votes
2answers
91 views

Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah)

is there any effective way to tune the query below? I have a filtered index on OrganisationID <> 0 Primary key is on another field, not relevant for this query ItemCode varchar(20). I have tagged ...
1
vote
1answer
25 views

Move a database (from decomisioned server) to an existing server with heavy ongoing loads

We have a situation where one of our database approx. 60 GB in size with transactions running every 2-3 mins 24*7 has to be moved, since the server it is on is going to be decommissioned. Now, we ...
4
votes
3answers
285 views

In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

One way to determine the executing stored procedure is to use "dynamic management" methods, like so: SELECT sqlText.Text, req.* FROM sys.dm_exec_requests req OUTER APPLY sys....
1
vote
0answers
18 views

How can I flatten value fields from a mapping table into a single column on an output report? [duplicate]

I have a mapping table structured similarly to the following: ID EntityID EntityName EntityCategory ValueID ----------------------------------------------------- 1 1 ...
0
votes
1answer
29 views

I created an index approx 5gb in size but the file system doesn't show any difference in free space

On a table with many millions of rows and two existing indexes (one clustered and one non-clustered primary key index) I created a third non-clustered non-unique index. Before creating this index, ...
2
votes
1answer
37 views

SQL Server 2005 still running after in place upgrade to sql 2012

We upgraded a default instance from SQL Server 2005 to 2012 today. But Components of SQL Server 2005 are still on the server. These includes the MSQL1. folder (it has all the data and log files), Mgmt ...
3
votes
2answers
154 views

“Upsert” trigger writing mass duplicates from SSIS

I have a problem with an "upsert" trigger, being triggered by a SSIS package with fastload. (FIRE_TRIGGERS is enabled) CREATE TRIGGER [dbo].[MP_VOL_UPSERT] ON [dbo].[vwMP_VOL] INSTEAD OF ...
0
votes
0answers
37 views

Deadlocking on two update statements

Server Info: Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Standard Edition (Build 7601: Service Pack 1) I have a table called LAB it has about 38 million rows in it. The primary key is a single ...
1
vote
1answer
39 views

are overlapping multi column statistics redundant?

I have run a database engine tuning advisor session, DTA, to optimize my database. The DTA has produced some statistics recommendations, however, I noticed some are contained in others for example ...
2
votes
2answers
265 views

Using the database engine tuning advisor - how do I generate the scripts for the recommendations?

I have run a server-side profile trace for over an hour to produce a .trc file with all activities in one of my databases. I have then passed this .trc trace file as a parameter to the database ...
2
votes
1answer
113 views

Troubleshooting memory pressure - how to avoid paging out SQL Server?

I have received the email below that states that an alert has been fired and a significant part of SQL Server process memory has been paged out should I raise the Minimum server memory to 1740 ...
4
votes
1answer
61 views

Verify the importance of statistics? Database with auto-create stats works?

Working on SQL Server 2005, I want to use the database engine tuning advisor to help me tune my webserver. First thing I do is to create a server side profile trace and run it for about an hour. ...
0
votes
0answers
16 views

sp_procoption seems to be failing on SQL 2005 instance

We have a SQL script that creates a Stored Procedure which is responsible for creating a SQL trace. On SQL Server 2008, the script runs fine, the SP gets created and the SP is configured to ...
4
votes
1answer
142 views

Actual Number of rows is too high even with new tables

I have a query based on two newly created tables in SQL Server 2005. I have index created on these tables. But when I write a query with JOIN condition, the number of actual rows returned is too high ...
2
votes
3answers
83 views

TSQL for “Ignore databases were the state is not online”

I have a few custom jobs on a mirrored SQL Server 2005 and they fail from time to time because of automatic failover. Is there some T-SQL code that I can write into my automated jobs or do I just ...
2
votes
2answers
73 views

Deleting multiple rows based on the first instance of the value

I have a table with the below structure. I would like to delete all of the records that records that match ColA for any of the ColB's that have a ColC that has an even-numbered value. But only for the ...
1
vote
0answers
25 views

Server error in reports application

I have a server with SQL Server 2005 installed with the latest service packs. SQL Server 2012 express was been installed on same machine and seems to have broken the Reporting services service. When I ...
-1
votes
1answer
38 views

Jobs scheduling to fetch data from multiple servers using SQL Server Agent [closed]

I have 2 servers - server-1 and server-2, both have an identical database. I have scheduled a job on server-1 thru SQL Server Agent (both servers have SQL Server 2005) which will open table1 on server-...
1
vote
1answer
26 views

How to convert different string value to respective 'YYYY' int format?

I'm inserting values into table from xml as: DECLARE @XMLRESULT XML DECLARE @tbl_XMLResult TABLE ( RowID INT IDENTITY(1,1), Status VARCHAR(50), Address VARCHAR(250), ListPrice INT, ...
2
votes
0answers
40 views

how to get column name from cross reference query

I used a script from here: http://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies that Milica Medic posted and it works GREAT thank you :) my question is how do I ...
0
votes
0answers
37 views

Transactional replication with push subscriptions (No errors but not working)

I have a very simple replication process set up. SQL Server 2005 One publisher, one distributor and two subscriptions Subscriptions are push I created the snapshot, it reinitialized to the ...
3
votes
1answer
106 views

Index fragmentation increased significantly after rebuild

I've googled this and I know that some people say index fragmentation doesn't matter, but they go on to describe scenarios where it might matter. Mine might be such a scenario. I have an index on a ...
1
vote
1answer
27 views

Run subquery once / optimize join with subquery without needing to use temporary table

Until today I thought I had the hang of optimizing queries with joins on subqueries. But I've been trying to optimize a query and on a whim I tried an old method that I thought I didn't need to use ...
2
votes
1answer
60 views

stored procedure can select and update tables in other databases - minimal permissions granted

I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum. This was ...
2
votes
1answer
24 views

ON clause of JOIN specific syntax?

I've been a bit curious about the syntax of the JOIN operation, especifically the ON clause where we set the condition. Let's say we are performing a join on an existing table and a table we have just ...
0
votes
0answers
52 views

How to drop partitioned index in SQL Server 2005

I am unable to drop partitioned index due to partition scheme and partition function existence. Table has a Primary Key, a Clustered Index and non clustered indexes. I am running the following query:...
2
votes
2answers
59 views

How to create a range of dates

I have a table named CN in SQL Server 2005: ID Startdate Enddate 1417 2015-09-30 09:45:00.000 2015-10-10 09:45:00.000 973 2015-09-14 11:03:00.000 2015-09-24 11:03:00.000 I ...
4
votes
1answer
123 views

Datetime as date only

I understand SQL Server 2005 doesn't support DATE and so casting a datetime as a date is not possible. What I want to do is list all records with a datetime within an entire day of the current week. ...
0
votes
0answers
42 views

SQL server is replicating to wrong subscriber

I have a publication which is intended to push data to a subscriber over the internet from a remote server to our onsite local subscriber but it ends up on a wrong subscriber.Here is the scenario: ...
3
votes
1answer
67 views

Grant Permission to Execute Set Transaction Isolation Level

I'm a part-time accidental SQL Server 2005 DBA whose full-time job is developing business intelligence solutions, but where the BI intersects with the database, it falls in my 'stack'. We have a ...
7
votes
3answers
518 views

Risks involved in upgrading to SQL Server 2008 R2

We have quite a number of SQL Servers that need to be upgraded from version 2005 to 2008 R2. Work is planned before the middle of the year as Microsoft is ending its support for the same. The 2005 ...
-4
votes
1answer
62 views

Unable to create a table type on SQL Server 2005

I have created a type defined as following: CREATE TYPE [dbo].[TypeListingDatePrice] AS TABLE( [RowID] [int] NULL, [ListPrice] [int] NULL, [SoldPrice] [int] NULL, [PendStatusDOM] [int]...
6
votes
3answers
318 views

Updating a view on multiple joined tables

Since MSDN does not say much, what happens exactly if I execute the following query? update claims set status='Awaiting Auth.' where status = 'Approved' Can I use the ClaimStatusName column of the ...
3
votes
1answer
89 views

SQL Server Service Broker - CHECKDB found consistency errors, how could this happen?

I encountered a weird SQL Server Service Broker consistency problem, namely I have a queue that holds messages (status=1, Ready to receive) which are lying there for a very long time. In the ...
0
votes
0answers
21 views

Mirror Suspended - can't detatch

We have a site where they have MS SQL 2005 and we are mirroring a database. At some point around Xmas it's stopped mirroring after an unscheduled reboot. Trying to resume just throws an error, and ...
1
vote
2answers
119 views

Transforming multiple rows into a single row

In my company, I need to join table A to table B: Table A is the sources table Table B is the empty table I want to know that how can I do it and the result like table B? Source table A: ID ...
1
vote
0answers
28 views

Design improvement on multiple references

Im having some 'perception' problem, maybe... I got this table linked multiple times with another small table and maybe is more of a personal problem with the way it looks but I feel there might be a ...
1
vote
1answer
112 views

Will transactional replication break if there is a heavy update/delete query run on the publisher DB

For these specifications: Publisher: (prod OLTP DB server) SQL Server 2005, database size : 220 GB also acts as the distributor Subscriber (Prod reporting DB server): SQL Server 2005, DB gets ...
3
votes
1answer
228 views

Reattaching MDF files recovered from RAID 5

We had been running a SQL Server 2005 server for many years at our company when just recently, we ran into some trouble. The server was configured with RAID 5 and had one hot spare disk in the array. ...
5
votes
2answers
815 views

Insert custom header row in BCP output

Long story short we have a database here that manages some employee data such as email, first name, last name, etc. Our company bought into this SAP Based expense report system :| that needs an ...
2
votes
3answers
322 views

Replicating data between SQL Server 2014 to SQL Server 2005 - temporary solution

I will be installing a new SQL Server 2014 database system, which is based on our current SQL Server 2005 database system. The new system will be on a completely new infrastructure when we go live, ...
3
votes
2answers
471 views

Find the days difference between two dates per month

I have a table 'data' in which there are two columns i.e. Startdate and Enddate. Startdate is the date when an employee joined working in a company and Enddate is the date when he left. I need to ...
1
vote
2answers
58 views

Getting Only What Changed from SQL Server 2005

We have a 17 store system which are on SQL Server 2005 Standard Edition and on Windows 2003 server. We currently have the following process: At each store we have an SQL job which runs a procedure ...
7
votes
1answer
119 views

Unique identifier field with a conditional

I have a database that's not in production, so the main table being CustodyDetails, this table has a ID int IDENTITY(1,1) PRIMARY KEY column and I'm looking for a way of adding another unique ...
2
votes
1answer
92 views

Can I truncate log in simple recovery mode? [duplicate]

I have a few SQL Server 2005 databases in simple recovery mode. The disk volume in which my transaction logs are stored had only 1GB free, when I started a stored procedure (massive delete in a single ...
7
votes
1answer
392 views

Is it possible to restore SQL Server DB from transaction log (no MDF)?

We had a SQL Server 2005 server that housed roughly 200 low/no traffic archive databases, and that server was not backed up to any other device. The database files (MDF and LDF) were stored on a RAID ...