This tag is for SQL Server 2005, major build version 9.00.xxxx.

learn more… | top users | synonyms (2)

5
votes
3answers
714 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
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
5k 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 ...
4
votes
2answers
500 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
221 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
747 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
3answers
2k 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 ...
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
3answers
155 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
862 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
708 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
4k 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
180 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 ...
2
votes
1answer
27 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
77 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
1k 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
252 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
220 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
340 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
431 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
2k 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
6k 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
283 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
1answer
3k 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
145 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
3answers
94 views

Clustered Index improvemens for particular queries

I found a table that looks like this: CREATE TABLE [dbo].Table1 ( id INT primary key IDENTITY (1, 1), [idUser] INT NOT NULL , [Amount] INT NOT NULL , [Attempts] INT NOT NULL , ...
1
vote
1answer
377 views

Procedure to wholly migrate SQL Server 2005 to a new server

I'm tasked with 'rebuilding' a database server, because the old one is dying. To reduce potential for the new server f**king up, we had decided to rebuild the server as similar as possible to the old ...
1
vote
2answers
190 views

Linked Server Has Run Out Of Memory

So I have a stored procedure that calls an AS400 Linked server. Now, the stored procedure will execute successfully. However, it is used in a SQL Agent Job and each time the job runs, it gives the ...
1
vote
1answer
477 views

Cannot shrink transaction log

Bit of a headscratcher here. I have a db that has less than 1GB of data, but a 40GB log file. Transaction logs are backed up daily, and there is not a lot of activity on this database; approximately ...
1
vote
1answer
900 views

How to avoid locks by large transactions?

We have a large application running, that requires cleanup for performance reasons. This was however not forseen when designing the application. Basically, the delete is executed from a stored ...
1
vote
1answer
643 views

Defrag or Backup+Restore

I have an MDF file that was autogrown from 1MB to 40GB in 10% increment. The file is heavily fragmented now, and I want to defrag it with minimum downtime. I have a plan that I am not sure will work: ...
1
vote
1answer
189 views

Follow up to certificate sharing question

Follow up to the post Sharing certificates encryped by password between DBs and instances I created a certificate with a password CREATE CERTIFICATE testcert ENCRYPTION BY PASSWORD = 'test123' ...
1
vote
1answer
181 views

Hard Drive Configuration for my setup

I have a fairly large transactional database (100GB) with a lot of users. The database access is solely via stored procs that make heavy use of temp tables, table variables, cursors and other fun ...
1
vote
2answers
885 views

Restore System Databases from MDF/LOG files

We recently experienced a hardware failure that caused our SQL 2005 server to take it in the dump. The good news is that we have the MDF and LOG files for all the databases, the bad news is that we ...
1
vote
1answer
751 views

Cartesian Product SQL / Inserts with multiple Values Clauses

I want to create cartesian product sql query.But,I'm getting the error.Where I wrong? alter table #Personel ( Ad varchar(100) ) alter table #Masa ( MasaAdi varchar(100) ) insert into #Personel ...
0
votes
1answer
147 views

How to know the Database user Login History from begining? [closed]

I need to know one particular user's login and logout database history from beginning in SQL SERVER 2005. Can anyone help?
0
votes
0answers
563 views

Cannot recover the master database. SQL Server is unable to run

Right, I powered off development server because it was hanged, when restarted directory services database was corrupted, so fixed it, then figured out that IIS Manager's metadata.xml is messed up, so ...
0
votes
3answers
91 views

Access to tables in SQL Server

I would like to know if there is a table where accesses to tables are saved, per user or per system. I'm using both SQL Server 2005 and 2008.
0
votes
1answer
516 views

How can I install a provider for linked server?

I have a provider for a linked server called PervasiveOLEDB.11.0 installed on a sql server 2005 machine. It always me to have a connection to a pervasive sql database. I would like to use this same ...