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

learn more… | top users | synonyms (2)

6
votes
2answers
5k views

Recover accidentally deleted rows from a table

My client has accidentally deleted all the records of a table from a test database. This test database is in SQL Server 2005 Express and we don't have a backup. Is it possible to restore the deleted ...
2
votes
1answer
739 views

How often do you back up the distribution database for transactional replication when sync with backup is set to true for the distribution database?

I'm trying to figure out how often to back up the distribution database when "sync with backup" is set to true. If I understand this correctly, sync with backup ensures the distribution database can ...
6
votes
2answers
577 views

“SELECT TOP N” returns the wrong results

Supposing I have a table foo similar to: ident int, primaryKey, auto_increment someNumber int statusId int ... where the someNumber column is, except for a few special cases, equal to ident. (in ...
2
votes
1answer
4k views

SQL Server 2005 collation issue during installation

Every time I try to install SQL Server 2005 on to a Windows 7 machine it installs properly but the collation installed is sql_latin1_general_as instead of sql_latin1_general_cp1_ci_as. I have read ...
1
vote
2answers
1k views

How to troubleshoot SQL “The timeout period elapsed prior to completion of the operation or the server is not responding.”

I'm trying to troubleshoot a timeout issue that occurs on one of our SQL 2005 servers. The database responds normally for about a day until we start to see sql timeout errors. After that all ...
1
vote
0answers
657 views

Can the service-broker cause SQL Server to crash when using SQLDependency

I have C# application that uses SQLDependency to get updates from a message table. I have tested it quite extensively and worked great in the development environment but when I ran it on the ...
2
votes
1answer
203 views

Enable SSMS in SQL Server 2005 Developer Edition

We just had an upgrade of all our computers here, and on one of them we loaded our licensed copy of Visual Studio 2008 and SQL Server 2005 Developer edition. I then loaded the service packs (4 for ...
1
vote
2answers
171 views

SQL Server 2005 datacube processing in high transaction system

We currently have a highly transactional SQL 2005 database which is growning by around 1.2 million rows weekly. On top of this we have a SSAS datacube which is set to process every two hours for ...
6
votes
2answers
4k views

How to make a unique column with default value of UUID

I have a key column for internal use which is just a increasing integer, but I would like to have a second unique column which is a UUID, but I don't know how to have a function called for the default ...
17
votes
1answer
2k views

Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT?

This is a followup question from: http://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically I'm still having deadlock/timeout situations in ...
2
votes
2answers
865 views

Why is query using Clustered Index when it shouldn't?

Let us presume I have a table named Category in a SQL Server 2005 database. Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)). There is obviously a clustered ...
1
vote
1answer
268 views

Copy database schema from SQL Server 2008 to SQL Server 2005 [duplicate]

Possible Duplicate: Downgrading from SQL Server 2008 to 2005 Is it possible to copy the database schema from SQL Server 2008 to SQL Server 2005, I don't need data from the tables, just ...
1
vote
1answer
29 views

How do I change the text in the export format dropdown on SQL Reporting server

I am looking for a way to change the text in the export format dropdown of a report on the SQL Reporting server (2005). For example, currently, one of the dropdown items is named: Web archive. I would ...
5
votes
1answer
765 views

How can I design a table for quick versioning of textual data?

Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content ...
7
votes
2answers
481 views

SQL Server schema intact but every record from every table is gone

We recently set up a new virtual machine running Windows Server 2008 and slapped a copy of SQL server 2005 on it. The server had running and in use without a hitch for about two weeks. The only real ...
0
votes
1answer
500 views

Small Squares or Blocks after string in table field

My problem. I'm running SQL Server 2005 in a Development (Dev) and Production (Prod) environment. I created a view that extracts data from a Navision database to SQL using a pass-through query ...
12
votes
6answers
2k views

Identical(?) SQL Server 2005 machines; query takes 2sec on one, 15min on the other

The Environment: We have two 32-bit Windows Server 2003 R2 machines running SQL Server 2005. The hardware configurations are identical servers with Xeon 5160 CPU, 4GB RAM, and 13GB RAID0. AWE and ...
2
votes
2answers
870 views

Error in in sysft_Name while restoring database

I have a .bak file which gives the following error while restoring: System.data.sqlclient.sqlerror:The path 'E:' has invalid attributes. It needs to be a directory. It must not be hidden, ...
3
votes
3answers
1k views

SQL Server 2005 database engine login failed error?

I installed SQL Server 2005 Enterprise Edition on Windows xp. This account exists in administrators role.But I can't login to SQL Server database engine using Windows authentication method or sql ...
1
vote
1answer
2k views

SQL Server 2005 database engine login failed error? [duplicate]

Possible Duplicate: SQL Server 2005 database engine login failed error? I installed SQL Server 2005 Enterprise Edition on Windows xp. This account exists in administrators role.But I can't ...
3
votes
2answers
237 views

How to know dependency on a table

I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem ...
10
votes
2answers
18k views

Why “Begin Transaction” before “Insert Query” locks the entire table?

I am using SQL Server 2005 Express. In a scenario, I added Begin Transaction command just before an INSERT statement in a stored procedure. When I executed this stored procedure, it locked the entire ...
17
votes
3answers
16k views

Is it possible to restore sql-server bak and shrink the log at the same time?

We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it ...
5
votes
1answer
2k views

sqlcmd to run large SQL file causes high CPU usage

I am using sqlcmd.exe to run a large SQL script, and the server CPU usage has been at 100% for the past 5 hours. The SQL script file has a size of 156 MB. I am running the script on SQL Server 2005. ...
7
votes
4answers
934 views

DDL contention on TempDB

I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type ...
1
vote
2answers
997 views

Designing a database tables for the following scenario

I just got following entities Employee, Client and Company. Where Employee and Client have one to many relationship with Company ie A single employee may be mapped to attend to many companies and ...
1
vote
2answers
2k views

why does my pivot give me an error?

Msg 207, Level 16, State 1, Line 1 Invalid column name 'FullName'. select FullName, ['1-Urgent'], ['2-High'], ['3-Medium (3000)'] from (select FullName,AltBusinessSeverity, total from Mdata) AS ST ...
1
vote
1answer
177 views

Migrating or Synchronizing SSAS Changes

Assume you have 2 SSAS databases, DB1 and DB2, that are identical. Now they branch off. Changes A and B are made to DB1 and changes C and D are made to DB2. Is there an easy method to migrate ...
2
votes
1answer
2k views

Export data into word documents

My boss has a kindle and likes to save a lot of stuff to his clippings. Currently I'm going through and cutting out each clipping and pasting it into a seperate word document with the title of the ...
5
votes
3answers
551 views

sql script repository [closed]

I am a fresh DBA just being promoted, I am looking for few good sites which constains T-sql Scripts for DBA tasks. Is there a common place from where you can get a collection scripts to perform ...
3
votes
2answers
425 views

Unable to BackUp Database on SQL2005

I have a database in MS SQL2005 that I can not backup. Whenever I try to create a backup of the selected database through SQL Mgm studio I get this error: The Backup of full-text catalog in not ...
2
votes
1answer
2k views

SQL Server 2005 Easy way to add all users to a database role

I have three application databases: A, B and C. Users of A and B are all users in C; the users from A are in the public role with only SELECT permission, while the users from B are in a role with more ...
1
vote
1answer
180 views

Using the name of a table contained in a field of another table?

I have a table (MyTable) with varchar columns named SourceID and ReferenceID where SourceID contains a table name and ReferenceID contains a key value for the source table. Is it possible in a query ...
5
votes
3answers
739 views

Which is quicker: Select of existing row vs Update where no row exists?

Which would run the fastest? Select from a table using the primary key (integer, clustered index, 1,000,000+ rows) Attempt an update on a different table, where the row does not exist? (where ...
1
vote
1answer
659 views

How do I load them as varbinary(MAX) format into SQL Database?

There are 1000 picture that are jpeg format.How do I load them as varbinary(MAX) format into SQL Database?But at the same time I want the bulk insert functionality.
2
votes
2answers
1k views

Determining maximum table size, type of engine and type of indexes supported

How many types of indexes are available with SQL Server 2005 and SQL Server 2008? MySQL has a variety of options like: Hashed, BTree, etc. What are the choices in SQL Server? I also want to determine ...
0
votes
4answers
1k views

Why Clustered Index property is enabled on few columns and not on others?

I heard that there can be only one Clustered Index? I may be wrong, but I am sure I read somewhere. In one of my SQL Server 2005 table, I have following columns: SerialNum ------- BigInt -------- ...
1
vote
1answer
101 views

sql 2005 - trace capturing all events

I am trying to capture all statements/SPs and RPCs (either they finish or fail) on a SQL 2005 server using SQL trace. Are the following events enough? Exceptions Stored Procedure RPC:Completed ...
2
votes
3answers
4k views

Backup Compression in SQL Server 2005

I noticed that simply zipping my backup files reduces the space used by 70% My idea was to, initially, backup the databases to a local server; then, using Bacula or Cobian (I don't have a budget), ...
5
votes
2answers
3k views

Changing Domain on SQL Server

I currently have some SQL Servers that sit on a sub-domain. We are eliminating that sub-domain and moving everything to the regular domain. As far as SQL Server configuration goes, is there anything ...
5
votes
1answer
4k views

Unable to kill SPID

I have a SQL Server 2005 Std (x64) with SP4 that has a procedure I cannot seem to kill. If I view sys.dm_exec_requests I see SPID 103 with wait_type of LCK_M_SCH_M, a status of SUSPENDED and command ...
2
votes
1answer
162 views

SQLCMDMODE from SSMS. Can I set -b mode when using :r to include files?

As far as I read msdn there is no variable that corresponds to the -b flag of the command (on error batch abort). I found no to enable this mode inside a script when running in sqlcmd mode. Is there ...
3
votes
2answers
1k views

SQL 2005- stored procedure performance troubleshooting

I am investigating performance issues for a stored procedure on SQL 2005 server. This is how I intend to do it: Analyze the stored procedure for any bad TSQL practices (like count * etc) Run it ...
3
votes
1answer
109 views

Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view?

Sorry for the long question, but let me give you my scenario. I have a DB that houses data exported from another DB. The containing tables are copies from the other (non-SQL) database. Each column ...
4
votes
2answers
1k views

Will creating partitions reduce locking and how do we implement this in sql-server?

A client wants our application to process more data faster so arranged a meeting with their dba to discuss options. This application generates quite a lot of data that is used for reporting. Before ...
15
votes
1answer
2k views

What types of corruption can DBCC CheckDB miss?

This question was prompted by this earlier post and my having a database filed away for future investigation that was restored following: BACKUP 'BrokenDatabase' detected an error on page (1:123456) ...
5
votes
2answers
10k views

can I resolve a deadlock with the rowlock hint?

I have a large delete stored proc and I've reproduced a deadlock in a situation where the deletes would not have deleted anything. It looks like the part of the stored proc that hit a deadlock was ...
2
votes
1answer
895 views

Optimising a Database Shrink (in terms of speed and reducing fragmentation)

We have a large database that has had a lot of older data stripped out, leaving primary datafile (all objects are currently in the primary filegroup) of about 80Gb containing a little less than 20Gb ...
3
votes
1answer
9k 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
6k views

What I can do to speed up Key Lookup in my Query

I am using SQL2005 and against them I calling query simila to this declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null); insert ...