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

learn more… | top users | synonyms (2)

3
votes
1answer
33 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
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
359 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
207 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
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 ...
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 ...
1
vote
2answers
103 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
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 ...
3
votes
1answer
124 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
113 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
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, ...
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 ...
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 ...
7
votes
1answer
103 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
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 ...
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 ...
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 ...
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 ...
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. ...
2
votes
2answers
89 views

Find the first datetime interval

I have a table in SQL with a column Recordtime and valueasreal RecordTime ValueAsReal 2015-11-30 12:59:00 195,6907 2015-11-30 13:00:00 203,8667 2015-11-30 13:01:00 198,0907 2015-11-30 ...
1
vote
1answer
79 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 ...
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 ...
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 ...
8
votes
2answers
213 views

Moving Databases to New Datacenters

My company is moving our infrastructure to new data center and I am trying to figure out the best way to keep the database on the new server in sync with the current production database until the new ...
0
votes
0answers
37 views

SQL Server Error Number 825 how to proceed

I have a Windows Server 2003 running on VMWare with an SQL Server 2005 installed. This morning I received 2 warning mails 4 hours apart about error 825. Both times the error was about an incorrect ...
2
votes
1answer
25 views

Track write requests on Read-only database

I have changed one of the databases to read-only in SQL Server 2005. I would like to track if someone tries to write to the database. Any suggestions on how to track such requests (end user write ...
1
vote
2answers
53 views

SQL Server - cannot drop idle job

Currently, I am transitioning a server from a company to another. They want to keep SQL Server installed on the server so we are wiping clean all the databases, maintenance plans and jobs. I'm ...
2
votes
1answer
36 views

Questions about DBCC FREEPROCCACHE

I used spAskBrent for troubleshooting a SQL Server 2005 performance issue. I found the problem was someone had ran DBCC FREEPROCCACHE. It caused low performance (very low PLE) on my database and ...
1
vote
0answers
67 views

SQL Server Alert System: 'Severity 17 - Potentially Fatal Error Insufficient Resources' occurred on myserver

I get a alert email with the title: SQL Server Alert System: 'Severity 17 - Potentially Fatal Error Insufficient Resources' occurred on myserver as 12:02 when I connect to myserver to check ...
2
votes
3answers
100 views

SQL Server Pagination using CTE

I have to apply paging on query as per requirement i need two extra columns other than query result 1. Total Records 2. Total Pages but i am trying to confirm that this the right way to retrieve ...
1
vote
1answer
27 views

Report rollbacks to log file

There are several reasons for SQL transactions to rollback. I want to log all of them to the SQL Server Log File so that I can find information about them at a later time in the SQL Server Log File ...
0
votes
2answers
47 views

From SQL Server 2005 to SQL Server 2012

I need to convert some databases from SQL Server 2005 to 2012; I know that you must convert it into 2008 before, but I can't rely on a SQL Server 2008 Enterprise installation: is there a faster way ...
0
votes
1answer
46 views

is_not_trusted = 1 after Error-Free WITH CHECK CHECK CONSTRAINT

I'd like SQL Server 2005 SP4 to trust a certain foreign key constraint. Executing an ALTER TABLE statement that enables + checks the constraint returns Command(s) completed successfully yet ...
10
votes
1answer
989 views

Why are multiple COUNT faster than one SUM with CASE?

I wanted to know which of the following two approaches is faster: 1) Three COUNT: SELECT Approved = (SELECT COUNT(*) FROM dbo.Claims d WHERE d.Status = 'Approved'), Valid ...
1
vote
1answer
53 views

Can't create database in SQL Server Express 2005

One of our clients is running Windows Small Business Server 2011, and they have a SQL 2005 Server Express instance (and several other versions also installed). Using Management Studio, running as ...
0
votes
1answer
68 views

Database Restore Not Working

I am trying to restore an empty MS SQL database 'Rental' to a new updated version of it. The backup of the database (named Rental_2) was taken from the 2012 version of SQL Server and I am trying to ...
3
votes
1answer
53 views

Remove the log shipping configuration on the secondary server, when the primary is no longer available

My primary server with SQL Server 2005 broke down, so I've started up the log-shipped database on the secondary server. The database is live on the secondary server, and I just need to remove the log ...
0
votes
1answer
54 views

Sub Query vs Table Filter

I have have been assigned to resolve Performance issue of a Stored Procedure. Select TableTwo.Column1, DatabaseTwo..TableThree.Column2, Sum(TableTwo.Column3) As Column3 --- Section 1 from ...
0
votes
1answer
20 views

How do I perform daily, monthly SQL Server 2005 maintenance?

Team, I have SQL Server 2005 and I am new to DBA role and wondering if there is any T-SQL Script/Stored Procedure out there that they will help me accomplish the following on regular basis - Daily, ...
0
votes
0answers
33 views

How to Split string into multiple values using best approach [duplicate]

We are trying to save result of a query in database by tokenizing a column but query is taking more than 3 minutes to finish his objective. My question is, do we have any faster way to tokenize a ...
0
votes
2answers
34 views

SA user password for software which automatically installs SQL Server 2005

I am testing a software which requires SQL Server 2005 for storing the data. The SQL Server 2005 database is automatically installed when I installed the software. During the software installation I ...
1
vote
1answer
69 views

How to create a stored procedure to update database after table edit

In a C# website application with a database, I set a gridView table so that I could edit the values in the table by row, but it appears that I need a stored procedure to do that. Below is the stored ...
1
vote
1answer
31 views

Batch process to delete table from SQL Server backup files

I have a set of full backups from a same SQL Server database saved in different files. For example: 01.my-database.bak 02.my-database.bak 03.my-database.bak I want to delete a specific table from ...
3
votes
1answer
186 views

Do not have permission to use 'dbo' when creating a view

I want to create a user with minimal required access to two databases within a server. The user will be used by someone from a third party integrating a Business Intelligence tool into our database. ...
0
votes
0answers
24 views

Checking Job Status on Linked Server (MS SQL 2005)

We have 2 MS SQL 2005 Server instance. Both of them hold our warehouse environment. We receive around 100 files from our ERP System everyday and SQL Job on our Live environment Loads them in to ...
1
vote
1answer
359 views

how to insert a list of varchar values, into a single column table?

In SQL Server 2005: CREATE TABLE __RADHE ( itemNo varchar(10) not null primary key clustered ); GO INSERT INTO __RADHE ??? '34926840', '34927020', '34927202', '34927384', '34927566', ...
15
votes
3answers
323 views

Slow update on large table with subquery

With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4. UPDATE [SourceTable] SET Bad_Count= ...