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

learn more… | top users | synonyms (2)

1
vote
1answer
773 views

Select statement to retrive xml output as below

Hi could someone provide me some clues or solution to retrive record sets like below note: i read the msdn documentation but leads me no where but hair loss :( Just for the assumption think i have 2 ...
2
votes
2answers
416 views

Database Mirroring Failure Reasons

I was wondering if there is any information on what scenarios might cause a DB Mirror to have to be recreated? I am attempting to research how sturdy the technology is and under what scenarios do we ...
3
votes
2answers
308 views

Comparing two XML query plans

Hopefully a simple one, if I have two (fairly complex, 30-40 node) .sqlplan files (from the same SQL text) what is a good way to compare them side by side? Or just have it tell me what it did ...
3
votes
1answer
405 views

Error moving SQL Server 2000 to SQL Server 2005 NOLOCK Error

I need to move a database from the SQL Server 2000 (32 bit) to SQL Server 2005.But,When I attacted my database to SQL Server 2005,it's gives me error.(NOLOCK error) Also , After I running dbcc check ...
2
votes
2answers
1k views

SQL Server DR Plan - Test Documentation

I have been tasked with creating a document for the testing of our DR/BC Plan for our SQL Servers. We are using DB Mirroring (tier-1) and transaction log shipping (tier-2) and for tier-3 databases we ...
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 ...
3
votes
1answer
6k views

How do I calculate values based on the previous row after skipping the first 12 rows?

I'm looking for help to write this query. I have included some sample data below. My initial premise is that all the values from the 13th row onward will be dynamic and the first 12 rows will be ...
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. ...
6
votes
2answers
1k 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, ...
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 ...
8
votes
2answers
2k views

How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle?

C# developer encouraged by management to write SQL Server stored procedures often produce procedures like this create table #t1 (...); insert into #t1 Select ... from table_a where ...; insert into ...
10
votes
3answers
4k views

SQL Server performance sudden degradation

I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full ...
4
votes
2answers
1k views

SQL Server agent Error

I am in a new shop and the below error started to show up on 2/22 repeatedly until 3/7. Event Type: Error Event Source: SQLSERVERAGENT Event Category: Alert Engine Event ID: 318 Description: ...
2
votes
2answers
662 views

How to index a many to many table most effectively

Should a many to many table be indexed? What kind of index would be best? Here's an example table: CREATE TABLE user_role ( userId INT, roleId INT ) --edit: drachenstern - I added the table def ...
5
votes
3answers
973 views

Sub queries or joins?

Whats better in terms of SQL server efficiency; to use sub queries or joins? I know uncorrelated are better than correlated sub-queries. But what about joins? The SQL becomes more readable and ...
6
votes
6answers
1k views

Which is better: identity columns or generated uinique id values?

I am in middle of a debate about whether it is better to use identity columns as a primary key on a DB table or have a generated value manually created. I am arguing for the sql managed identity ...
0
votes
0answers
492 views

Whats better: identity columns or generated unique id values?

I am in middle of a debate about whether it’s better to use identity columns as a primary key on a DB table or have a generated value manually created. I am arguing for the sql managed identity ...
6
votes
1answer
5k views

How can I assign different random values to each row in a SELECT statement?

Please look at this code: create table #t1( id int identity (1,1), val varchar(10) ); insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 ...
3
votes
1answer
119 views

Permissions Dropped after Package Failed

I created a local db and then set up a one-off job to copy the live db to this new db. Or at least that's what I thought I'd done. The logs suggest that the job thought it had to create the db on live ...
3
votes
1answer
681 views

Which fillfactor is used by ALTER INDEX REBUILD?

From technet I got the impression, that I either have to explicitly set the wanted fillfactor or when I do not specify it, the default fillfactor 0 is choosen. For tables I know to get rows added ...
5
votes
2answers
1k views

How to convert primary keys into clustered primary keys for a great number of tables keeping referential identity

After discovering, that I better change the primary keys from non clustered to clustered. Cf this question about clustered indexes on identity columns, the next step is how to do this in a prcatical ...
17
votes
4answers
2k views

Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns. The database development started in ...
5
votes
1answer
2k views

How to install SQL Server Express 2005 with Full text search

I am in need to Install SQL server 2005 Express edition with Full Text Search capability. I don't know what to download and the order of installing the Database management system. Could someone ...
8
votes
2answers
519 views

How to implement paging in an effective way?

I have a database query which could result in a big result set. The client which displays the data receives the data over network, so the idea was to minimize the amount of transferred data by ...
1
vote
2answers
748 views

How to group a resultset by day?

I have a table with a LastUpdated field that is DateTime and wanted to know how can I group months of data collected by chronological days such that I can derive the number of records in each day ...
1
vote
3answers
133 views

Is having a 92MB MDF with a 58MB LDF cobo ok? How do I manage the log to keep things running well?

I have jobs in place to trim tables used for history and logging so to keep them trim. I want to make sure I don't neglect the log file. How and what can I or should I do to keep the log file in ...
2
votes
1answer
979 views

Is it possible to specify Active Directory username and password in connection string?

My organization uses Active Directory to control access to our databases. Building web applications and as part of our CI process I would like to specify the credentials in the connection string. Is ...
4
votes
1answer
744 views

whats best practice for Log Truncation in SQL Server?

I have a production DB in SQL server and wanted to put the final touches after the functionality is completed. prior to shipping it out i want to make sure i have some clean up in the SQL server DB ...
9
votes
3answers
1k 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
2answers
138 views

SQL server 2005 backup statistic

I'd like to share with you this simple snippet (answering my own question) and I'm also curious how do you control your SQL server backups.
33
votes
2answers
76k views

How to the get current date without the time part

In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0
4
votes
1answer
12k views

trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?

I am trying to learn how to turn XML into heirarchical SQL tables. I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone ...
2
votes
2answers
5k views

How do I fix a login timout in SQL Server 2005 when using a linked server and a stored procedure called from a SQL Agent Job?

I have a sql agent job running on ServerA. This job executes a stored procedure ServerA.DatabaseA.dbo.SPROC_A, inside this SPROC there is a query that selects data from ...
5
votes
2answers
4k views

The SELECT permission was denied on the (View) object after explicit grant select on view to user

I have a database with a View Named '1098Statement' We are doing testing for an application with user name 'appuser'. I have tried the following solutions to granting select on the view and keep ...
4
votes
1answer
781 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 ...
1
vote
1answer
1k views

SQL Server 2005: Integration Services Stored Packages is empty. Why?

I am trying to import, export SSIS packages. I know they are there. When I query msdb.dbo.sysdtspackages90 I get 22 packages. But when I connect to Integration Services on the server, the stored ...
5
votes
1answer
355 views

Why can't I call my UDF directly? Why do I need to qualify it 3 levels deep?

I just created a UDF and testing it I found only works when I use this syntax SELECT [PMIS].[dbo].[fnIsReportingTo] (50,1132) I was expecting it to work like this: select * from fnIsReportingTo ...
5
votes
2answers
1k views

How do I replace this where clause with a join?

Typically when I see SQL that uses something like select * from employees where epmloyeeTypeId in (select id from type where name = 'emp') I replace the where with this: select e.* from ...
4
votes
2answers
468 views

How can I disable async IO or isolate one session that slows or freezes other sessions?

Each day I have a session that produces Avg.Disk Queue Length > 800 (5min interval). I have found that just before this moment Sql Server optimizer have made an error: it decides to use a ...
1
vote
1answer
354 views

How can I pivot this select?

SELECT sc.CID,sc.CodeName as OverviewText,scRAG.CodeName as RAGStatusText FROM StatusCode sc LEFT OUTER JOIN ProjectOverview po ON sc.CID = po.ProjectOverviewCID AND po.ProjectId ...
1
vote
1answer
110 views

a query that listing all rows and sizes of tables in a Database

I am using SQL Server 2005.My question is;I am looking for a query that listing all rows and sizes of tables in a Database.
3
votes
2answers
1k views

How can i detect whom a person reports to using a self-referencing employee table?

I have a typical self-referencing employee table. The data is hierarchical and I use the following UDF to encapsulate a simple CTE to derive an output of a particular person's direct and indirect ...
7
votes
1answer
345 views

Why would running ALTER TABLE on a column cause it to go from NOT NULL to NULL?

I am mass updating a SQL Server database. I am changing all our numeric(38,0) columns to int (yes, SQL Server scripts were created from Oracle scripts). Using SMO and C# (I am a sw engineer), I ...
3
votes
3answers
252 views

Does separating VARCHAR values based on size have a performance gain?

I am working with a team that is trying to implement an EAV system. They have decided to split the attribute-value tables out by type and they are debating using different tables for different size ...
2
votes
4answers
459 views

Why would i get a duplicate write error of auto-generated primary key?

Hi I'm not a DBA so forgive my ignorance. I don't have access to administrator tools etc. However, I do know that we have a live server which is approaching capacity ie 80%-90% full perhaps. What is ...
2
votes
1answer
517 views

SQL Server Analysis Services - who uses them? [closed]

We all know Sql Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Who has used this service in their ...
3
votes
2answers
105 views

How do you handle adding nullable columns to each other in a query?

Currently whenever I write a query that is adding columns that can contain nulls I resort to wrapping each field in isnull or coalesce, such as coalesce(score1,0) + coalesce(score2,0). Is there a ...
3
votes
2answers
645 views

Using CLR User Defined Functions in SQL Server

I have used CLR user defined functions for very specific instances, such as complex string handling, but generally didn't like them from a maintenance point of view (yet another place to hide code). ...
4
votes
1answer
347 views

what is the first schema checked?

I am a little confused right now. Can someone answer me with a simple answer to this question. I have a user [user_test] and has [db_owner] role assigned to him for one database [database_test]. ...
1
vote
1answer
3k views

Linked Server Problem

I have a MSSQL2008 and MSSQL2005 in my system.All SP's is up-to-date. 2008; Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft ...