SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

1
vote
2answers
2k views

Cursor doesn't print text when I tell it to

I have used WAITFOR combined with a cursor in the following script: create table orders(orderId int primary key,productId int,productName varchar(20)); insert into orders values(1,11,'book'); insert ...
6
votes
2answers
3k views

Dropping and recreating indexes for performance reasons?

I'm maintaining Somebody Else's Code. There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive. But it's ...
0
votes
1answer
338 views

Milliseconds to datetime

I have this statement EXEC [spProject_Update] @ProjectID = 496 ,@Name = 'Hangman Bollywood iPhone' ,@Description = '' ,@EstimateTime = 2674800000 I will get milliseconds in @EstimateTime. How can I ...
6
votes
2answers
1k views

Can't get SQL Server 2012 RTM LocalDB working on Server 2008/x86/ SQL Server 2008 machine

I have installed SQL Server 2012 and LocalDB on my development machine, and am able to connect to LocalDB using both the SQL Server Management tool 2012 and from within my winform application using a ...
2
votes
1answer
2k views

Why is SQL Server 2008R2 using more memory than the “max server memory” setting? [duplicate]

Possible Duplicate: Microsoft SQL Server 2008 Memory I've explicitly set the Maximum Memory value in SQL server 2008R2 to 13,000 MB, however when I check task manager I see it is using 14,071,...
3
votes
2answers
3k views

How to identify fragmentation level of the table data itself not the table indexes, and then defrag

Every where I can find descriptions on how to identify the fragmentation level of the table indexes, but I cant find how to identify the same for the table data iself. Also defragmentation solution is ...
4
votes
1answer
962 views

How to query EndDate value from ReportServer..Subscriptions.MatchData

In SQL 2008 R2, I'm putting together some administrative queries on SSRS metadata to check proper configuration, and other sorts of proactive things. While querying the ReportServer db, I've been ...
3
votes
2answers
1k views

why does my clustered index scan have ordered = false

When I look to at my query plan, I see plenty of clustered index seeks (yay!) and scans (not quite so yay!) but in some of them ordered = False. I will mention that we have parallelism involved, ...
2
votes
2answers
53k views

How to Get the Sum (total) of a column in a query result set

Is there way that I can get the sum of a column in my query result set by just clicking on the column (something like in excel) so that i don't have to copy and paste the column into excel to the see ...
5
votes
4answers
3k views

Microsoft SQL server 2008 and linked servers

I'm designing a set of databases which might need to use each other's stored procedures or tables. The reason that I'm creating separate databases is that in the future I want to be able to move any ...
2
votes
2answers
22k views

declaring cursor to loop over some values in SQL server 2008 R2

I have some business unit such as India, International, US, UK. I have to: Create a table variable (virtual in-memory table) with columns that match SPROC SP_Report resultset. Declare a cursor that ...
6
votes
3answers
11k views

Test if any fields are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
2
votes
2answers
93 views

can I get SQL Server to choose between a table and a view?

Our reports need to be live and quick - don't they all? The overwhelming majority of tables in our application database just have INSERTs - and the tables have incrementing primary keys. Our tables ...
5
votes
1answer
820 views

Are foreign keys better than indexes?

I have a replicated copy of an application database. I am exploring how to change the constraints and indexes to better serve our reporting requirements. Because this is a copy of the application ...
2
votes
2answers
3k views

Adding Timestamp to Exported Filename in SQL Server Management Studio

I am exporting to a CSV using a job with xp_cmdshell. I need the filename to include a timestamp. The code I currently have is... EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout "C:\...
0
votes
3answers
1k views

Is it possible to edit sys.messages texts?

Is it possible to add a new language to SQL Server 2008 and edit SQL error messages with message_id less than 50000?
2
votes
2answers
398 views

Configuring SQL Server profiler

Every 15 seconds or so, the application I am trying to profile fires: @@TRANCOUNT > 0 COMMIT TRAN IF @@TRANCOUNT > 0 COMMIT TRAN IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF ...
2
votes
1answer
906 views

Difference between SSAS 2005 and SSAS 2008

I'm asking this question since i'm planning to book a seminar, but the provider told me that they only offer trainings for SSAS 2008 or as individual training which are expensive. So my question: ...
5
votes
4answers
3k views

Best pratice to store dates group by months-year key par value

I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I ...
9
votes
2answers
290 views

SQL SERVER Storage of TinyInt

In SQL Server, why is a tinyint stored with 9B in the row. For some reason there seems to be an additional one byte at the end of the NULL bitmap mask. USE tempdb ; GO CREATE TABLE tbl ...
0
votes
2answers
2k views

Recommended plugin for SQL server 2008 [closed]

Is there any recommended plugin for SQL server 2008 to use when you are working as database administration, Business Intelligence developer or Database developer?
5
votes
2answers
1k views

How to automatically stagger transaction log shipping to minimize bandwidth usage peaks?

The following question pertains to Microsoft SQL Server transactional log shipping (TLS). We're using SQL Server 2008 R2 SP1, although the question is probably relevant for all recent editions. ...
44
votes
4answers
14k views

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other. Our ...
1
vote
3answers
7k views

How should I Schedule SQL Server 2008 queries to run every month?

I need to schedule some queries to run at the beginning of every month (for monthly reports) and I read that creating a JOB in sql server 2008 was the way to go. Unfortunately, I don't have the rights/...
10
votes
2answers
4k views

Restore bak file to smaller mdf and ldf database files

I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have: MyDatabase.mdf: 24.8GB MyDatabase.ldf: 114.6GB This ...
1
vote
2answers
222 views

How to retrieve the definition behind statistics added to tables

Is there a way to programmatically retrieve the definition of each STATISTICS added to table columns and indexes. For both user added and system created indexes. There are many STATISTICS like '...
1
vote
3answers
182 views

Keeping an eye on contention

In SQL Server, I am using the following command to see blocked sessions: select * from sys.sysprocesses where blocked>0 This is super. The problem is I need to see what block sessions there ...
2
votes
1answer
642 views

Access Data that is stored in Primary FileGroup On SQL Server through DBLinks on Oracle

I am using dbLinks to fetch data from SQL Server 2008 to Oracle (10g)database. When I fire the command : select * from "test_table"@dbLink Oracle throws error : select * from "test_table"@dbLink ...
23
votes
4answers
10k views

SQL Server “empty table” is slow after deleting all (12 million) records?

I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
2
votes
1answer
659 views

do user_scans and no user_seeks mean the index is useless?

I'm digging into sys.dm_db_index_usage_stats and finding that a number of our indexes are used exclusively in user_scans, not user_seeks. Does this imply that the index is useless and should be ...
5
votes
2answers
188 views

Design for storing data only applicable to a small subset of records

So my boss and I are in disagreement how to best design our database for storing miscellaneous data for certain clients. Basically, we have a bunch of tables in some format similar to this (in pseudo-...
4
votes
2answers
923 views

Splitting a column in two and returning a summary of results

Lets take a very simple table Department | Staff Name ---------------------|--------------- Web - Electronics | Matthew Store - Electronics | Mark Web - Home | Luke Store - ...
1
vote
2answers
43k views

Error: Specified cast is not valid. (SqlManagerUI)

I am trying to restore database created on server to my localbox however i am getting 'Error: Specified cast is not valid. (SqlManagerUI)' error message. Server Info: Windows Server 2008 Standard ...
2
votes
1answer
1k views

Processing occasionally fails with “Unexpected end of file has occurred.”

I've got an Analysis Services database that's used for reporting on email activity. It mainly counts incoming and outgoing messages, who they're to and from, etc. Fact table: 11,367,910 rows Address ...
2
votes
1answer
80 views

Diagnose slow SQL in SQL Server

I am not a DBA so please be patient with me... Is it possible to turn on a trace on SQL Server which will tell me any SQL statement that takes longer than 2 seconds to execute, when it happened and ...
2
votes
3answers
137 views

Table size and table partitioning on a single drive server

Suppose I have a big table on a server with only one drive. The table may grow up to a half TB (hundred million rows). I think the purpose of the partitioning is mainly for maintenance purpose because ...
2
votes
3answers
4k views

How to recover the suspect mode database in SQL Server 2008?

To recover the database from suspect mode executes the following query. EXEC sp_resetstatus ‘yourDBname’; ALTER DATABASE yourDBname SET EMERGENCY DBCC checkdb(’yourDBname’) ALTER DATABASE yourDBname ...
1
vote
2answers
7k views

Cannot get a message to go to a SQL Server Service Broker Queue

I am trying to setup a simple SQL Server Service Broker messaging setup. I am following the MS tutorials, but I cannot get the following code to work. Everything runs fine, but the message does not ...
2
votes
2answers
421 views

Can I move a system database from one version of SQL server to another?

We are moving our databases from one computer running SQL Server 2005 to a different machine running SQL Server 2008. I am following the steps found here, in an attempt to copy over the master ...
2
votes
2answers
781 views

parallelism repartitions, ordering, and hash matches

I am just starting to delve into query plans and recently learned that "hash matches" are bad. I know it's rare for something to be so absolute, though, and I want to determine if I have a bad hash ...
1
vote
1answer
192 views

Impact of miss-ordered fields in a composite, clustered primary key?

I have tables where the primary key is something like this: -StoreID -BusinessDate Typically, there is one row for every store (~100) for every single day. One row for each store is inserted on a ...
0
votes
1answer
8k views

sql server - could not allocate space for object

I am trying to retrieve the first 1000 records of my view and am getting this error message: Msg 1105, Level 17, State 2, Line 2 Could not allocate space for object 'dbo.SORT temporary run ...
9
votes
6answers
4k views

Tables with hierarchy: create a constraint to prevent circularity through foreign keys

Suppose we have a table that has a foreign key constraint to itself, like such: CREATE TABLE Foo (FooId BIGINT PRIMARY KEY, ParentFooId BIGINT, FOREIGN KEY([ParentFooId]) REFERENCES ...
4
votes
2answers
1k views

Performance degradation after rebuilding indexes

I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and ...
6
votes
5answers
48k views

How to install SQL Server 2008 R2 Profiler [duplicate]

SQL Server Management Studio doesn't have it on the Tools menu in my case, as is reported in some blogs and forums. Apparently you somehow either got it or not, but how did you get it, if you did, is ...
4
votes
1answer
666 views

How to change database owner for login and database created inside stored procedure?

I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my SProc creates the new login and new database but will ...
3
votes
3answers
940 views

Database growing like crazy - what do I do?

I have an Epicor 9 ERP database that I recently was put in charge of (new job). Server is running Server 2008 R2 with SQL Server 2008 R2 64bit. The main production database started at 16gb 7 months ...
4
votes
2answers
3k views

Get failed SQL Server agent job?

How to get a list of failed job run last night? I only find the following powershell script. What's the SQL equivalence? dir $psPath\Jobs | % { $_.EnumHistory() } | ? { $_.RunStatus -ne 1 }
1
vote
2answers
3k views

Should I put the error handler inside the loop, or the loop inside the error handler?

When a procedure is looping over a statement that could raise an error condition, should I put the loop inside the error handler, or put the error handler inside the loop? I want the procedure to run ...
2
votes
1answer
2k views

Activity Monitor Showing Long Duration for WAITFOR

Using Activity Monitor, I am consistently seeing the following query showing an average duration of over 300 seconds: WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, ...