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

learn more… | top users | synonyms (2)

0
votes
1answer
55 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
1k 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
74 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
99 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
103 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
58 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
25 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
35 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
38 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
79 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
34 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
234 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
26 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
905 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
395 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= ...
2
votes
0answers
34 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 ...
1
vote
0answers
114 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 ...
1
vote
0answers
30 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 ...
0
votes
0answers
36 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
0answers
125 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 ...
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
177 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 ...
0
votes
1answer
46 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 ...
0
votes
1answer
29 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?
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 ...
3
votes
2answers
180 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 ...
5
votes
2answers
325 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
149 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 ...
0
votes
1answer
35 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 ...
0
votes
1answer
36 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 ...
2
votes
0answers
38 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
35 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
115 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 ...
0
votes
0answers
95 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 ...
-3
votes
1answer
58 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 ...
4
votes
1answer
74 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
261 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 ...
1
vote
1answer
49 views

What transaction logs are for

I'm confused about tranasaction logs for SQL Server. I was under the impression they can be used to roll the database forward. For example say I made a software release on Sunday, I added three new ...
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 ...
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
139 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 ...
1
vote
2answers
401 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 ...
1
vote
1answer
66 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 ...
2
votes
0answers
53 views

Why does SQL Server 2005 only use an IS lock in this scenario?

I am attempting to examine the locking behind different queries but with the same output to avoid deadlocking situations. I have the following code: SELECT MIN(ID), TextData FROM PKKEYTESTER ...
19
votes
2answers
683 views

Optimization issue with user defined function

I have a problem understanding why SQL server decides to call user defined function for every value in the table even though only one row should be fetched. The actual SQL is a lot more complex, but I ...
1
vote
4answers
2k views

How to select first row from a join that returns multple rows on the primary key

This is related to this question: Joining multiple tables results in duplicate rows I have two tables that I am joining. They share a key. The person table has one name per primary key but the ...
4
votes
1answer
157 views

Restoring database without worring about partitions

On our our production database there is a partitioned table called FullLog. This is updated via triggers so we know who changed the database and when. Which is a problem for me when I restore the ...
1
vote
1answer
83 views

Duplicate records referenced across different databases

I have a table holding 'People' data (about 70K records) that coexists with an 'Address' table so there's one AddressID per every person in the 'People' table. The idea was to centralize the data in ...
3
votes
2answers
143 views

Online index rebuild snapshot location and impact of filling index drive

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index. My question is where does the snapshot index ...