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

learn more… | top users | synonyms (2)

4
votes
3answers
5k views

How to see the value of a variable being used in an sql statement during a server trace?

I have a stored procedure which has SQL statements that take variables. For example, update customer set cust_id = @newcust_id where issuedate < @transferdate and cust_id = @oldcust_id The ...
4
votes
5answers
3k views

Can I restore a database to a point of time without backup?

I have an SQL 2005 database with logging set to FULL but without any backups. Is it possible to restore it to a point of time?
3
votes
2answers
1k views

Sharing certificates encryped by password between DBs and instances

Question: I am trying to create a single (self signed) certificate and deploy across many DB's and instances. I create and backup the cert with: USE MASTER CREATE CERTIFICATE DavesCert ENCRYPTION ...
2
votes
1answer
277 views

SQL server not using the memory allocated

We have noticed that for one of our SQL server with below configuration ,memory is not getting availed as allocated. SQL server 2005 SP4, 32 bit Windows server 2003 R2 SP2, 32 bit with 16 GB ...
2
votes
1answer
4k views

EXCEPT operator vs NOT IN

The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ? Does it do the same? I would like a simple explanation with an example.
2
votes
2answers
473 views

Check Index Fragementation Query

I'm completely stumped. I'm a relatively new SQL Server DBA so I'm at a loss with the following situation; hoping maybe someone had an idea. I have a new production SQL Server (Windows Server ...
1
vote
2answers
6k views

Shrink my .mdf file in SQL Server?

I have a 10 GB .mdf file with 250 mb .LDF. I want to shrink the 10 GB .mdf (if possible) I read about the dbcc command, but I read it in context of shrinking the .LDF (into any size desired using ...
-1
votes
0answers
395 views

All users missing after a detach/reattach [closed]

After detaching and reattaching a database to do a copy operation, all of the user objects are now missing from the database (I should add that the copy failed; according to the logs, there was an ...
17
votes
5answers
6k views

Why are periodic restarts required to keep my instance performing well?

We've got a production DB server on SQL 2005. Everything runs normally for a while, but after a couple weeks we see a notable performance drop. Only restarting SQL Server brings performance back to ...
11
votes
2answers
1k views

If I fail over one database, do the others that share the same mirror endpoint fail over as well?

We have two databases setup for mirroring on a single SQL Server instance: a test database and a production database. Both get mirrored to another server using the exact same endpoints. If I go into ...
9
votes
3answers
3k views

How to change the firing order of Triggers?

Realy I rarely use triggers. So I met a problem at first time. I have a lot of tables with triggers (2 or more for every table). I would like to know and change the order of firing triggers for every ...
8
votes
2answers
2k views

Displaying query plans in Server Management Studio

Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset: select top 10 sum(qs.total_worker_time) as total_cpu_time, ...
8
votes
1answer
474 views

Full Plan Cache Behavior

In SQL Server 2005, what happens when the plan cache fills up? I have done some research, and from bits and pieces gathered it seems that cached plans are given an "age" that consists of it's weight, ...
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 ...
7
votes
2answers
418 views

Why does this cursor produce results in the incorrect order?

I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes. The line ORDER BY 1,2,3 DESC; in the SQL ...
6
votes
1answer
11k views

Is ASYNC_NETWORK_IO wait type anything to worry about?

In looking at the list of sprocs that take long time to execute, one stands out as causing the most wait. However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the sproc transfers ...
6
votes
1answer
3k views

How do I shrink the physical Transaction Log file when it's the principal in a mirror?

We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking ...
6
votes
2answers
670 views

Most efficient way to return multiple aggregates in a single stored proc?

What is the best or most efficient way to get multiple aggregate result values? Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, ...
5
votes
1answer
1k views

Connecting to SQL Server using cached credentials

While migrating from SQL authentication (user-password) to integrated security I ran into an unexpected combination of requirements. SQL clients are services running under a LOCALSYSTEM or ...
5
votes
2answers
674 views

How can I run multiple concurrent versions of my application against the same database?

Background: I have multiple concurrent versions of my application running in my production environment. Depending on the user account that's used, the user will have access to a different version of ...
5
votes
3answers
799 views

Why is Row Overflow not working on SQL Server 2005

I have a SQL Server 2005 database with a table that has around 860 columns. Aside from the ID column all other columns are varchar(N) where N is either 100 or 500. Upon inserting a new row (via an ...
5
votes
3answers
3k views

Port number change

We are planning on setting a static port for SQL Server 2005/2008 failover clustering. Any guide on what port to choose/select for 4 nodes of each cluster?(Active/Active) I am also thinking some ...
5
votes
3answers
2k views

Which problems arise, declaring the size of all varchar parameters as max in stored proc?

Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time. Increasing just the size of a column in a table is a rather simple task. ...
4
votes
1answer
4k views

sys.fn_varbintohexstr returns NULL if varbinary data is more then 2000 as datalength

sys.fn_varbintohexstr works fine if VARBINARY data length is upto 2000. If it is greater then 2000, function sys.fn_varbintohexstr returns NULL as result. What I am missing ? DECLARE @testvarbinary ...
4
votes
2answers
651 views

Can I manually reset the “internal status flag for a full database backup”?

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day. This worked last weekend, however this weekend the differential ...
4
votes
3answers
298 views

how to copy data from SQL Server table to excel Spreadsheet thru Linked Servers

I try to copy data from a table in SQL Server 2005 into a excel spreadsheet. Since that sheet is linked to some external charts, so I have to overwrite the existing data with the updated data from SQL ...
4
votes
1answer
818 views

Need help with ASYNC_NETWORK_IO

We have a SQL Server 2005 server that is serving up 20 websites, one of which is our custom CMS, and a few other micro sites. I am new to this and just started looking at the databases here. The ...
4
votes
2answers
1k views

SQL Server 2005 : How to use pivot instead of a bunch of case statements?

Please show me how I can modify this SQL select to use pivot instead of all the case statements. SELECT t.ProjectId, COALESCE(MAX(ExecutiveChampion), 'n/a'), ...
3
votes
4answers
9k views

How to find all positions of a string within another string

How can I find all the positions with patindex in a table or variable? declare @name nvarchar(max) set @name ='ali reza dar yek shabe barani ba yek ' + 'dokhtare khoshkel be disco raft va ali ...
3
votes
3answers
171 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million). We're ...
3
votes
1answer
968 views

Why do we have to use SWITCH in table partitioning?

I'm a beginner in SQL. I'd like to know what is the importance of SWITCH in table partitioning? I've created one table partitioned month-wise, with a total of 12 partitions. The ranges are: ...
3
votes
2answers
878 views

SQL Server 2005: Full-text search space requirements

Is there a way to calculate the physical drive space required for a full-text search catalog? Space is cheap, but I'd like to know what I'm getting into. I'm looking at a table of about 200 articles ...
3
votes
1answer
6k views

moving large number of tables to different filegroups

An existing database has a large number of tables stored in the PRIMARY filegroup. I want to move theses tables and their indexes on different filegroups automatically, depending on the "prefix" of ...
3
votes
2answers
202 views

SQL 2005 Permission Precedence

Very simple question - I have two database roles (Basic, Admin). Say I explicitly deny the Basic role from deleting from table A and grant that to the Admin role. If I am a user who is in both ...
3
votes
1answer
8k views

Running an SSIS package without the SSIS service installed

On a SQL Server 2005 instance in our DMZ, we have BIDS installed but not the SSIS service. If I have an SSIS package on that server, can I still schedule to run it without SSIS service? I tried ...
2
votes
1answer
194 views

Linking SQL Server tables to a PostgreSQL Server

I have a SQL Server 2005 instance and also a PostgreSQL 9.3 running on the same network, I would like to know if its possible to somehow create or have 'views' on a database within the PostgreSQL ...
2
votes
2answers
211 views

Upgrading a database from SQL Server 2005 to 2012 uncovered a syntax error

I recently upgraded from SQL Server 2005 to 2012. During validation however, a bug was discovered. A certain trigger was coded as follows: CREATE TRIGGER [dbo].[trigger] on [dbo].[foo] FOR UPDATE, ...
2
votes
1answer
2k views

Best way to migrate a database involved in bidirectional replication

I have two SQL Server 2005 that are to be migrated to SQL Server 2008 R2. The original pair of servers contain a database that are involved in bidirectional transactional replication between them. In ...
2
votes
2answers
296 views

How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped. The table hierarchy is as follows: Order table contains OrderReference(PO), OrderID ...
2
votes
1answer
235 views

Active Directory domain being shutdown— Do I have to update my DBO accounts?

I manage a SQL Server machine (with SQL Server 2000 and 2005 installed) that was first created on an Active Directory domain that we are transitioning away from and are about to shut down. All of my ...
2
votes
3answers
442 views

Capture all queries that use hints

How can I capture or list all queries that use hints and have executed since the last instance restart? I am using SQL Server 2005 Standard Edition.
2
votes
1answer
559 views

How to change ANSI_NULLS option for a table?

I have a table that mistakenly has ANSI_NULLS option set to OFF. I'd like to set it ON for this table. The table has millions rows of data. Is it possible to change this option without creating a new ...
2
votes
2answers
640 views

Is it possible to view the maintenance plans created in SQL Server 2000 in SSMS 2005?

Is it possible to view the maintenance plans created in SQL Server 2000 (also running on the same SQL server 2000) in SQL 2005 Server Manangement Studio? I have the server added in my SSMS, but when I ...
2
votes
2answers
1k views

SQL Job in idle state

A created a new SQL Job in SQl Server Agent. I followed the steps mentioned in the article http://msdn.microsoft.com/en-us/library/ms187910.aspx Still it is in idle state. PLease note that the ...
2
votes
2answers
3k views

An inconsistency was detected

Am running asp.net application.Am searching records.if i click the view button i got this error, an inconsistency was detected during an internal operation in database(database name) on ...
2
votes
1answer
309 views

Health checking via Database Mail in SQL Server

I have a few Sql Scripts that I would like to run every day on my Sql Server(s). How can I query multiple times and have the output similar to the following: Health check for <server> Query ...
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 ...
2
votes
1answer
4k views

Location of Maintenance Plan's Back Up Database Tasks information (SQL Server 2005)

I would like to know where in the database or on the file system the information about Back Up Database Task in the Maintenance Plans Tasks. I can find the Job in msdb.dbo.sysjobs I can find the ...
1
vote
1answer
331 views

table partition in order to solve frequent deadlock issues - how would I know if it will work?

I use sql server 2005 enterprise edition and have a subscriber DB - part of a transactional replication - that is used for Business Intelligence purposes - they run reports out of it, and SSIS ...
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 ...