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

learn more… | top users | synonyms (2)

1
vote
4answers
5k views

Attaching MDF without LDF

I only have an .mdf file of my database which is not attaching. I am using the following command: EXEC sp_attach_single_file_db @dbname = 'Test_Data', @physname = N'D:\Test_Data\Test_Data.mdf'; ...
4
votes
1answer
2k views

823 errors on tempdb

Have a couple Fiber channel SAN-attached cluster servers which have been intermittently reporting errors like: 2012-05-09 16:31:54.13 spid74 Error: 823, Severity: 24, State: 2. 2012-05-09 ...
0
votes
3answers
69 views

Lost my SQL Server 2005 host, SQL Server 2005 database import into SQL Server 2012?

My SQL Server 2005 host died, but the database related LUNS were on a SAN and still exist. Is there a way to use my SQL Server 2012 host to recover that database or import it in some way?
4
votes
2answers
550 views

What characters are word breakers in English for SQL Server 2005 and 2008 R2?

I can find what DLL supports English word breakers by using sp_help_fulltext_system_components but I have not been able to find an actual list of the word breaking characters for English (like blank, ...
4
votes
1answer
786 views

SQL 2005 Full Recovery Mode Point in Time Restore w/out Backup

We have a database that is in full recovery mode. The database is not in production yet so does not have a backup plan in place and is thus not being backed up. In other words, there is no existing ...
4
votes
4answers
4k views

How SQL Server handles concurrent requests?

Suppose a SQL Server table has two MailID columns, with max value = 5. If two different users at the same time execute insert queries that try to insert Max(MailId)+1 into the MailID column, is it ...
5
votes
1answer
7k 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 ...
1
vote
1answer
579 views

What causes 'Unknown token received from SQL Server'? [closed]

I have a SQL Server 2005 box with a classic ASP front end (IIS6) calling a stored procedure that uses dynamic SQL. It works except for a single day of data that somehow throws this error even though ...
0
votes
0answers
17 views

How to identify the creator of the temporary table in SQL Server 2005 [duplicate]

On my server many users performing complex queries using tempdb. Sometimes a failed specifying boundary values ​​obtained are very large tables. So I want to know who created this temporary table. ...
3
votes
1answer
456 views

Does openquery use distributed transactions?

Does openquery in SQL Server 2005 use distributed transactions? If so, in which cases does it?
0
votes
1answer
7k views
3
votes
2answers
867 views

Backup name in Maintenance plan

I used to have SQL Server 2005 installed on my server. In my maintenance plan, the backup name format was like this: mydatabase_backup_yyyymmddhhss.bak. Now I am using SQL Server 2012 but I am ...
16
votes
2answers
43k views

Sql Server database in recovery: How to stop, Take ofline or delete?

I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so. I need to either stop the recovery, or kill it in some way. I dont care one ...
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 ...
18
votes
5answers
8k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
3
votes
2answers
3k views

Check if all record columns of 2 Tables/Query are equal

In SQL Server 2005, I would like to make one query that checks if all record columns of 2 tables are the same, Example: declare @tbl1 table(col nvarchar(50)) declare @tbl2 table(col nvarchar(50)) ...
1
vote
0answers
62 views

New database server hardware and software inserts slower than old setup [duplicate]

We are upgrading our database hardware and software. The previous server is about 5 years old and is running SQL Server 2005 while the new one is SQL Server 2012. Every hardware component is faster in ...
1
vote
2answers
215 views

Is outputting to both a result set and a temporary table possible?

I have a stored procedure that I need to both output the result set to the user and use a piece of information in the result set to possibly output other result sets. Here is a basic schema of what I ...
10
votes
3answers
2k views

How can I tell what recovery model my SQL database has?

Is there a SQL command I can run to determine the recovery model of my database? I want to know if its full recovery or not.
3
votes
1answer
2k views

App domain is marked for unload due to memory pressure

In my sql server log (2005 SP4) I have this message every couple of seconds : "AppDomain XXX [DataBase.dbo[runtime], YYY] is marked for unload due to memory pressure" Our server : Windows Server ...
1
vote
4answers
217 views

Database growth causing issues sending backup over network

We take a full database backup on our primary SQL Server, and send that file over our network to a remote site where it is recovered on a backup SQL Server. We use this backup database for slow ...
0
votes
1answer
492 views

Best Isolation Level to avoid deadlocks in Sql Server [duplicate]

I need execute un update statement over an sql server table, this table is used by another process at the same time. because that sometimes deadlocks ocurs. wich Isolation Level do you recomend for L1 ...
2
votes
2answers
633 views

Running query progress indication

I have a query: SELECT * FROM OPENQUERY ( oracle, 'SELECT * FROM [some_table]' ) It runs for about 7 hours. I only need to run this query once to import data into a new system when we ...
0
votes
1answer
69 views

XML Network Call to Application from SQL Server 2005

We don't really have a DBA here, just a semi-experienced SQL Developer and me. I've only got about a year's experience myself and we're the only programmers for our small business. We're trying to ...
3
votes
1answer
156 views

Change SQL Server 2005 from Standard Edition to Workgroup

I have been tasked with performing an edition change from SQL Server 2005 Standard Edition to SQL Server 2005 Workgroup Edition, on Windows Server 2003 Standard Edition. Due to a previous failed ...
5
votes
3answers
5k views

SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012

Yesterday we migrated a SQL Server 2005 database to SQL Server 2012. We made a full backup and restored it on the new server. Problem now is that the database on SQL Server 2012 is much slower than ...
1
vote
1answer
233 views

standard steps to Restart Seconday server in Logshipping setup

I have to reboot my Logshipping secondary server. what are the steps need to be taken on primary and secondary servers to make the reboot clean and without hurting logshipping. env : SQL server 2005 ...
0
votes
0answers
47 views

Mirroring traffic looks normal but externally reported bandwidth of mirroring traffic is much higher

We have a live sql server in one location, running MS SQL Server 2005 Enterprise on Microsoft Windows Server 2008. We have a similar server in an alternative location (connected by vpn) serving as a ...
0
votes
1answer
1k views

Restoring a SQL Server 2008 R2 backup on SQL Server 2005

I'm getting an error whenever I restore a testing database on SQL Server 2005 that was backed up from SQL Server 2008 R2. It seems that I cannot restore from a higher version of sql onto lower ...
1
vote
0answers
134 views

Inside stored procedure, set column names of returned query according to variable

I have a query which I'd like to reuse, instead of having many different copies of it just for the sake of changing the column name. The report (for example) uses the same query, but there are either ...
1
vote
1answer
76 views

Database never maintained

Just moved from wrong forum i paste here my question: I just got a new client recently migrated from SQL Server 2000 to SQL Server 2005 due to vendor application limitation to new releases. Vendor ...
0
votes
0answers
61 views

How to analyze incoming DML in SQL Server 2005?

I have database and specifically one table that is used for batch data transfer from another organization. With each run the table is truncated and rows are re-inserted. I want to handle that process ...
0
votes
0answers
71 views

Database migrated to SQL Server 2005 with 80 compatibiltiy from SQL Server 2000 experiencing locks

I am supporting an application whose code my company did not write, and was originally developed back in 2000-2002, by a company that no longer exists. The code, while not great to maintain, ...
0
votes
1answer
1k views

Cannot access Properties page of a database

I'm in the process of decommissioning a database server and migrating the databases from one server to another. I was attempting to get the the properties of the database and received a SQL Error ...
1
vote
1answer
63 views

How to transform this result set?

I have the result set above that I stuffing into a table variable @ApptTemp, and trying to transform it into something similar to the one below. Note that of the 4 rows that have a description of ...
0
votes
1answer
29 views

SQL Server 2005 Whole-Migration, which databases should I recreate?

In relation to my question about SQL Server 2005 migration procedure, I am re-creating the databases on the new server. I noted that the old server has the following databases (outside of System ...
0
votes
4answers
308 views

SQL Server 2005 - 2 sets of backups - 2 maintenance plans?

I did my best to search for an answer to this question and couldn't find a scenario quite like this. I'm a fairly new DBA in the sense that I haven't had a ton of training, I basically took the job ...
1
vote
1answer
319 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 ...
4
votes
2answers
240 views

Query tuning to meet timeout settings [closed]

We have an web app with a 30 second time out on search screens. I've analyzed the original query and added a missing index for one of the query's 9 joins. Other things like expected and actual ...
1
vote
0answers
54 views

Will installing WebMatrix (and Consequently SQL Server CE) Break My Current Installation of SQL Server 2005?

We have a server that has (and needs to keep) SQL Server 2005. We are hosting intranet sites on one server (server 1), but have a database on another server (server 2). One of these sites uses AJAX ...
5
votes
1answer
2k views

SHRINKFILE best practices and experience [duplicate]

Preamble: In general it's a big no-no, but believe me that are rare cases when space is really needed. For example Express Edition is limited to 10GB. Imagine that you discovered that with a data type ...
1
vote
2answers
160 views

One of the Database Ownerhas been Removed and corresponding property changes in sql server

A database has been created on SQL 2008 by a user named user1 ,on dec 2013.Today ,we are going to remove that user(/owner) from the server.What will be the owner property of the database show then?
5
votes
1answer
124 views

Why are zero length spaces being returned from a Union All against an empty table in SQL Server 2005 but not SQL Server 2012

Supporting an existing VB6 app with a SQL Server 2005 back end and we're migrating our database server to SQL Server 2012. Created a backup of the production database and restored it into a dev ...
1
vote
2answers
60 views

Will creating an index improve performance on a full scan [closed]

Will adding an index on a Large data warehouse table (8Million rows, 200+ column) make any difference to the query time if all I am doing is selecting all rows from the table ? Thanks
1
vote
2answers
180 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 ...
2
votes
2answers
84 views

Replication between SQL Server 2005 - 2005

I'm hoping to get some help with a SQL Server 2005 replication issue. In short, in replication monitor, (On the Publisher), on the 'Distributor to Subscriber History' tab, I see two lines under ...
6
votes
3answers
3k views

Do you know an easy way to generate one record for each hour of the past 12 hours?

I have a report that shows the count of events for the past 12 hours, grouped by the hour. Sounds easy enough, but what I am struggling with is how to include records that cover the gaps. Here is an ...
0
votes
1answer
372 views

SQL Server 2005 - Generate and Bulk Insert 3 million rows across 2 tables

I have a task to programmatically generate 3 million different rows of data and insert them into a database table for test purposes. I have written a VB.NET program that handles the generation of row ...
0
votes
1answer
71 views

What is the MS_PerfDashboard schema in msdb?

We just migrated an old reporting server (running on 2005) to 2012 and some of the database performance monitoring jobs have started failing. I looked into them and found that they use a couple of ...
0
votes
1answer
612 views

Looping through string, adding all numbers e.g. '123' = 1+2+3, demo with working loop included

This works to output the string 123456 as: 1 2 3 4 5 6 Code: declare @string varchar(20) declare @index int declare @len int declare @char char(1) set @string = '123456' set @index = 1 set @len= ...