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

learn more… | top users | synonyms (2)

2
votes
1answer
636 views

How to add another table to publisher and subscriber without reinitializing the subscriber?

Is there chance to add another table in SQL Server 2005 transactional replication without reinitializing subscriber. Thanx.
1
vote
3answers
68 views

SQL Server - Index Including PK

Lets assume we have a table as follows: PK -- nonclustered primary key Col1 -- unique clustered Col2 Col3 Is this index: nonclustered index on (Col2, Col3) any different from this index: ...
3
votes
1answer
2k views

Is it better to delete then insert, or to update then insert in SQL server?

I'm using SQL server 2005. I have a table that is basically a list of ids with timestamps that is recording the last time an event happened for that id. I have a list of ids in a table variable, and ...
2
votes
2answers
1k views

How does DBCC SHRINKFILE() affect backups?

I have a pile of SQL 2005 databases that have been around for a very long time and, for varying reasons, experienced transaction log autogrowth. Regular backups now keep the transaction log usage ...
8
votes
5answers
786 views

sql server concurrency performance

We have encountered some performance issue on our production environment. we found that when active sessions go up above 25, the usage of CPU reach to 100% and it takes long time to go down. The ...
2
votes
2answers
631 views

SQL Server Replication Agent Profile -Output Parameter

I'm trying to add the -Output parameter to my log reader agent for transactional replication and getting this error: Msg 21805, Level 16, State 1, Procedure sp_MSvalidate_agent_parameter, Line 92 The ...
1
vote
2answers
3k views

Statement could not be prepared

I've got a SQL Server 2008 server set up with a linked server to a 2005 server. On the 08 server i've got a view set up that's pulling data from the '05 server. This scenario works from my dev '08 ...
3
votes
3answers
960 views

How can I find the VLF count for a SQL 2005 database without running dbcc loginfo?

Is it possible to find the number of VLFs in a database's transaction log without running DBCC LOGINFO? DBCC LOGINFO seems a little heavy and verbose when count is the only information my t-sql script ...
3
votes
2answers
4k views

SQL Server 2005 tempdb Time out occurred while waiting for buffer latch type 4 (pagenum)

Three separate processes have failed in the last two days with very similar errors: Time-out occurred while waiting for buffer latch type 4 for page (1:1189832), database ID 2. Time-out occurred ...
4
votes
3answers
761 views

Table structure for many custom profiles

I'm currently developing an bulk-email campaign manager that will allow customers to upload as much information as they would like on their subscribers. For example, I have a basic profile that is ...
2
votes
1answer
173 views

How important is the build number on a SQL Cluster

I did not actually perform this install, nor am I responsible for fixing issues on this particular cluster. I just happen to be checking it and found some things that puzzle me. My point in this is ...
12
votes
3answers
4k views

Is it possible to backup and restore part of a database in sql-server?

We have a sql-server 2005 database that we regularly transfer from our client site to ours. This takes a long time because we don't have a direct connection and have to transfer the file over their ...
4
votes
2answers
1k views

SQL Server 2005, Large Binary Storage

I'm looking at trying to store a large amount of files in my SQL Server 2005 instead of saving them to a file system. I recently read a white paper suggesting that anything over 1M should be stored ...
5
votes
3answers
1k views

Managing multiple SQL Server databases

My company has many branch offices and each branch has a DB server. Some have SQL Server 2005 and some others have SQL Server 2008. The schema is the same for all these servers. Whenever I want to do ...
1
vote
0answers
153 views

SQL Server and Perfmon [duplicate]

Possible Duplicate: SQL 2008 Server agent cant see perfmon counters I'm trying to setup SQL Server Perfmon counters on a Windows Server 2003 with SQL Server 2005 SP3. I'd like to run ...
4
votes
4answers
5k 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 ...
2
votes
2answers
436 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 ...
4
votes
4answers
903 views

Speed problem with a simple select statement

I'm having a problem with a select statement taking way too long to run. I would like to know if I'm doing something wrong or if it taking this long makes sense. I am using Microsoft SQL 2005 ...
1
vote
4answers
17k 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 ...
3
votes
2answers
5k views

SQL Xml returning empty root tag on empty record set

I am working on xml explicit to generate user defined nodes in xml output using Sql Server 2005 Express edition. The below code works fine, just that i couldn't manage to generate a empty node when ...
1
vote
1answer
121 views

SQL Server Query against a View failing

I have a view definition that references a linked JDE server. Querying the view is now giving some very strange results. If I perform a SELECT * I get all the results (619 rows) back but if I try ...
2
votes
1answer
171 views

Possible Pitfalls of Setting Up Kerberos to Permit Cross-SQL Database Access Via AD Trusted Connection

We are hoping to the implement Kerberos on our Active Directory (2003, Functional Level 2) so that our SQL Server 2005 databases can communicate with one another when a client is using Active ...
14
votes
3answers
11k views

How does shrinking a SQL Server log file affect performance?

I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
2
votes
1answer
445 views

SQL Server Logon Trigger causing problems with the Distributed Transaction Coordinator

I've created the following simple logon trigger on my SQL Server 2005: CREATE TRIGGER LOGON_RESTRICTION ON ALL SERVER FOR LOGON AS BEGIN PRINT 'Hello World' END GO Which seems to be working fine ...
7
votes
3answers
749 views

More Databases Per Sql Server 2005 Instance

Our company currently hosts about 400 databases per SQL Server 2005 instance. 500-600 databases and we see performance start to deteriorate. Database size ranges from 50mb to 40-60gb in the extreme, ...
8
votes
2answers
4k views

SQLCMD Mode in SSMS and @@variable expansion

When using the SQLCMD mode withing SSMS (not from the commandline), is there a way to assign the current server and instance to a variable? This is different and distinct from assigning ordinary TSQL ...
3
votes
2answers
4k views

How to open queries into a selected database in SQL Server Management Studio 2005

My situation: Using Microsoft SQL Server Management Studio 2005, I connect to a server -- I’ll call it Prod -- using Windows Authentication. Within this Prod server there are multiple databases: ...
1
vote
2answers
1k views

How to make inter-database queries with dynamic SQL Server database name?

We have two database servers with SQL Server 2005, with two databases each. The secondary server have two SQL Server instances: one for development and other for reporting. Both instances on secondary ...
2
votes
1answer
117 views

Index issue on order by column while applying Union all

I have two tables with 6 columns in 1st table and 6 in the second one. I have to combine the result of these two tables in order to make them in one set. Below are the structures of my tables: CREATE ...
2
votes
2answers
908 views

sp_help_jobsteplog fails when run as a @query of sp_send_dbmail

I have a SQL 2005 stored proc that sends an email with some job history: msdb.dbo.sp_send_dbmail @recipients = 'me@here.com', @Subject = 'job history', @Body = 'check this out...', ...
2
votes
5answers
196 views

How can I store function names to be executed on a subset of rows?

I have a table, accounts, which contains the vast majority of the data I am concerned with. accountTypes contains different types of accounts (Business, Personal, etc.). I want to perform calculations ...
1
vote
2answers
245 views

dm_db_missing_index_details - does the order of equality_columns matter

I've been investigating some databases using the dm_db_missing_index_group_stats DMV, and have a question about the equality_columns field in the dm_db_missing_index_details view. MSDN says that this ...
6
votes
5answers
7k views

SQL Select taking too much time to execute

It's a simple select from a temporary table, left joining an existing table on its primary key, with two sub selects using top 1 referring the joined table. In code: SELECT TempTable.Col1, ...
2
votes
1answer
2k views

Tool to Convert MySQL DB to MS SQL 2005 (Especially stored procedures) [closed]

Is there any tool to convert MySql db to MS SQL 2005. I am mostly interested in converting Stored Procedures. There are 100s of procedures and writing them again in T-SQL is a big job. Thanks.
1
vote
1answer
158 views

Duplicate record problem

create table #Personel ( Ad varchar(100) ) create table #Masa ( MasaAdi varchar(100) ) insert into #Personel values('deneme1') insert into #Personel values('deneme2') insert into #Personel ...
9
votes
2answers
1k views

SQL Agent powershell context reference

At my new job, we have multiple named instances on each servers. Server1\Dev Server1\DevIntegrated Server1\QA I have a SQL PowerShell script in the works that calls out to the OS, invokes ...
1
vote
1answer
782 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 ...
2
votes
2answers
433 views

How to query status changes in time series?

Given create table rating ( ID int identity(1,1) primary key, PersonID int, Ratingdate date, rating varchar(2) ); insert into rating values ( 1, '2010-08-04' , 'A3'); insert ...
1
vote
2answers
316 views

how to generate scripts for database objects specific to an application?

There was a application which, earlier, pointed to a database. It used a specific set of tables. Those tables had triggers, constraints, etc. There were also stored procs which insert data into these ...
4
votes
1answer
782 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 ...
3
votes
1answer
566 views

Sql Server Performance Degradation in moving from 2005 to 2008

I have sql server 2005 setup for my performance bench marking exercise and I have put same database on another server which has sql server 2008 and exact replica in terms of hardware configuration. ...
2
votes
2answers
262 views

SQL Server 2005 Row update problem

After I pulled these datas what I wanted from a SQL Server Database,I can't update on row.I'm receiving the following the error.
1
vote
2answers
346 views

SQL server 2005 SP4 differential backup is failing

We have an SQL server 2005 with SP4, x86. When we attempt to take the differential backup, we are getting the following error message. Cannot perform a differential backup for database "ABCD", ...
3
votes
1answer
2k views

MS Access displays alle columns in SQL Server tables as “deleted”

I have created an ODBC connection to a SQL Server 2005. When I link to the tables from MS Access, some tables have only "deleted" data. This means the rows and columns are displayed, but all data ...
5
votes
2answers
1k views

How can I corrupt a table in SQL Server?

I have to write a program that detects corrupted tables and tries to repair them. For this I need to corrupt tables regularly. Is there anyway to corrupt tables manually?
5
votes
5answers
624 views

SQL Server Backup Process Tuning

I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. ...
11
votes
4answers
14k views

Hard and Fast rule for include columns in index

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post Why use the INCLUDE clause when creating an ...
1
vote
1answer
174 views

Logical page error while attempting to insert data

I am getting the following error while trying to insert a row in a table: Attempt to fetch logical page (1:12032) in database 19 failed. It belongs to allocation unit 72057594069450752 not to ...
1
vote
1answer
251 views

Changed stored procedure causes VB runtime error

ALTER Procedure [dbo].[HRptForm3A] @StrCond Varchar(1000), @SelYear int, @Type varchar(1) as Declare @Tbl varchar(30) if ( @Type = 'O') begin set @Tbl = 'TblResult' ...
2
votes
2answers
376 views

update stored proc ony fields that are passed to the proc and leaving others alone

i have a stored proc that does a simple update (see below). i want to update only fields passed and not the ones that are null. that is to say if i dont get a parm value for somethign i dont want ...