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

learn more… | top users | synonyms (2)

0
votes
0answers
18 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: ...
1
vote
2answers
104 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 ...
5
votes
1answer
515 views

SQL Server 2005 Replication

I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription. server 1 windows 2003 server 192.168.10.1 connected ...
3
votes
1answer
34 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 ...
1
vote
1answer
3k views

Mirroring SQL error log - Error: 8474, State: 11

I'm getting these errors in SQL error logs: An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far ...
6
votes
1answer
54k views

CASE statement with IS NULL and NOT NULL

Is there any better way to write the lines below in SQL Server 2005? CASE WHEN (ID IS NULL) THEN 'YES' WHEN (ID IS NOT NULL) THEN 'NO' END AS ID_Value,
2
votes
3answers
453 views

Problem to access the SQL Server

I have SQL Server 2005 which is installed on server machine. I was working fine on SQL server before I change the server machine IP. I changed server machine IP to host the Web Service on it. But ...
-1
votes
0answers
27 views

SQL Server 2005 patching issue

We are trying to patch SQL Server 2005 server with SP 4 cumulative hot fix 5324 for the first time in the server. Our patching wizard got hung for about 2 hours at this stage: Installing ...
7
votes
3answers
361 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
52 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] ...
6
votes
3answers
209 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 ...
1
vote
1answer
300 views

Risk in evicting node from SQL Server cluster without prior uninstalling of SQL Server binaries

I have to perform upgrade of a SQL Server failover cluster with several SQL Server 2005 instances running on two Windows Server 2003. Since the plan is to have servers running Windows Server 2008 and ...
6
votes
3answers
1k views

SQL Server parallelism for DML operations

I know SQL Server can use parallelism for certain DDL operation e.g. index rebuild or certain SELECT statements mostly likely in a data warehouse environments when query cost exceeds cost threshold ...
3
votes
1answer
18 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 ...
2
votes
1answer
200 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of ...
1
vote
1answer
414 views

Replication monitoring refresher for distribution job affecting performance

I have a performance problem due to a sql agent job executing dbo.sp_replmonitorrefreshjob. The environment is a 2 node Cluster with SQL Server 2005 SP2 and with transaction replication. The OS ...
5
votes
2answers
3k views

Does SQL Server have a Date_Format function?

All I found is a list of predefined DateFormat that I can choose from, like this As I remember in MySQL (and PostgeSQL too?) you can define your Date Format: DATE_FORMAT(now(),'&m_%Y') --for ...
0
votes
0answers
18 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 ...
6
votes
2answers
546 views

“SELECT TOP N” returns the wrong results

Supposing I have a table foo similar to: ident int, primaryKey, auto_increment someNumber int statusId int ... where the someNumber column is, except for a few special cases, equal to ident. (in ...
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 ...
2
votes
5answers
20k views

Retrieving row count without using Count function

I wrote a query below which shoots me a syntax error why would it do so, SELECT MAX('Row') FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS) Error Desc: Incorrect syntax near ...
5
votes
2answers
6k views

Issues with TempDB mdf file ever increasing

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and ...
7
votes
4answers
920 views

DDL contention on TempDB

I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type ...
15
votes
4answers
5k views

Is it possible to backup and restore part of a database in sql-server?

We have a sql-server 2005 database that we regularly transfer from our client site to ours. This takes a long time because we don't have a direct connection and have to transfer the file over their ...
2
votes
1answer
153 views

SQL Server 2012 - Dropping Snapshots takes hours

We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL ...
1
vote
1answer
45 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 ...
4
votes
2answers
240 views

Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

We are running into an issue where if we look at the avg row size (0.2kb) x number of rows (200) inserted in one of our table, the size should grow by around 40 KB. However the total used space has ...
35
votes
4answers
27k views

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index ...
5
votes
2answers
116 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 ...
3
votes
1answer
125 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. ...
2
votes
3answers
60 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, ...
11
votes
3answers
37k views

SQL Server 2005/2008 UTF-8 Collation/Charset

I can't find option(s) directly to set UTF-8 rellated Collations/Charsets in SQL Server 2005/2008, same as is possible to set in another SQL engines, but in SQL Server 2005/2008 are there only Latin ...
0
votes
0answers
13 views

Replicating data from 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
194 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 ...
7
votes
4answers
70k views

Combine column from multiple rows into single row

I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
0
votes
0answers
24 views

sqlevn70.rll - Failed to Load Format String for Error 345

I have been receiving the following alerts from this error message as shown below: Operating system error: 317(The system cannot find message text for message number 0x345 in the message file for ...
7
votes
1answer
104 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 ...
1
vote
2answers
56 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 ...
2
votes
1answer
88 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 ...
4
votes
1answer
498 views

SQL Server 2005 Express in VMware causing very high CPU load

I'm having the problems described in KB937745 - very high CPU usage and the Application Log is reporting something like this: The client was unable to reuse a session with SPID SPID, which had been ...
4
votes
2answers
383 views

Disabled SQL Server Endpoint - now I cannot login

I have SQL Server 2005 on my local machine. During the studies of SQL Server network configuration, I logged into my SQL Server Instance and ran the following script: USE Master; GO ALTER ENDPOINT ...
11
votes
1answer
4k views

SQL Server TempDB behaviour in large memory environment

Reading over this question reminded me of a question I had a while ago. We have a SQL Server that has 512GB of RAM, the main database is 450 GB. We see quite a lot of action in TempDB (ok, I think ...
7
votes
1answer
362 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 ...
3
votes
0answers
51 views

Proc Cache Drop Assistance [duplicate]

We are noticing performance issues with one of our applications. When the application issues begin the procedure cache size goes from 4.5GB to 0. The plans are then rebuilt and still the application ...
13
votes
2answers
11k 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 ...
2
votes
1answer
65 views

Script file execution through Power shell

We are trying to automate few things, one of them is to deploy patch in one go on selected DBs of SQL Server, as per my knowledge Powershell is an option. But I have zero taste buds of Power shell. ...
1
vote
1answer
80 views

all tables used by a stored procedure and all sps that use a particular table - recursive cte

I am working on a script that gives me a list of tables that use a specific stored procedure. It also works on all tables used by a stored procedure. on the example below, all stored procedures that ...
14
votes
2answers
3k 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 ...
0
votes
1answer
101 views

Multiple criteria for including / excluding rows

CLARIFICATION UPDATE Unfortunately we report based on the 3rd party application and database so we cannot modify the datatypes since we do not own it. To help clarify, This report basically shows us ...
2
votes
1answer
29 views

Unpivot with rows converted to columns as well as columns converted to rows

I've tried to understand unpivot examples on the web but they don't quite result in what I'm trying to do, which is to have specific rows turned into columns and columns turned into rows. To try and ...