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

learn more… | top users | synonyms (2)

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
396 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
899 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
286 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
1k 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
179 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
636 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
655 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
916 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
42k 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
136 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
6k 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
403 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
767 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
7k 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
659 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
929 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
2k 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, ...
3
votes
2answers
8k views

How to lock a Row while I'm using it

I want to be able to Lock a row, select it, increment its value, and then release the lock. (without lockin the other rows, so that other connections can work with the rest of the table) I've found ...
1
vote
2answers
720 views

Create multiple files for each file group for table partitioning?

I am partitioning a table by date (month). The server has four drives. The partition scheme is defined as CREATE PARTITION SCHEME ByDate AS PARTITION ByDate TO ( [PRIMARY], [fg2], [fg3], [fg4], ...
2
votes
2answers
358 views

Found errors in table after executing DBCC CHECKTABLE command

I am trying to alter table to add primary key ALTER TABLE GeoCity ADD CONSTRAINT PK_GeoCity PRIMARY KEY(locId) and got this error message Msg 1750, Level 16, State 0, Line 1 then I run ...
1
vote
1answer
83 views

Where and with what should I be looking for a good hardware backup tool? [closed]

Until now, I was doing my SQL Backups using USB Hard disk, and copying that to my local computer. After that, I went zipping and burning onto DVDs. Now I want to push that on more professional and ...
0
votes
1answer
746 views

How to load more than one MS-Access-db tables with data in SQL-Server-DB

I'm just getting into SQL-server 2008 R2 ssis, so forgive me if this is a basic question. I'm building an application using integration service. One of my computer folders contains more than one MS-...
5
votes
2answers
558 views

Is there a setting to force an error when asigning a varchar longer than a variable?

I would like to get an error when assigning a longer varchar to a variable and no silent truncation. Are there any settings? This truncates silently: declare @code varchar(10) set @code = 'This is ...
5
votes
1answer
1k views

Using MSDTC in SQL Server 2008

I used Hyper-v to install one windows 2008 server machine, and copy the image to create another one and change the computer name, both of them are in the same domain, and I use a domain user as ...
0
votes
3answers
213 views

Alternative of replication in internet disconnection period of time [closed]

I'm looking for an alternative of merge replication that work without internet connection, something like red gate sql data compare but not in uniq direction.
11
votes
5answers
8k views

What's a good tool for visualizing SQL Agent jobs? [closed]

I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating. What tools out there have you found helpful? Offhand, there are three things I'd like to see in such a ...
1
vote
1answer
80 views

Is it safe to change the name of a database?

I have a SQL Server database which has data for several customers/devices. We are changing the prefix of our website (asp.net) and our application dynamically loads the proper connection string based ...
3
votes
2answers
3k views

What is the difference between a SQL Login and a Database User in terms of security?

I am currently working with a consulting agency to set up a new production environment. They have set up a separate SQL login for each database. However, there is no corresponding database user ...
4
votes
1answer
90 views

Issue with CAST using linked servers (possible bug?)

So I've got a some SQL Server 2008 DBs arranged in a star schema. Server A is the one users submit queries to. On Server A there is a DPV called Detection that's basically a UNION of table Detection ...
2
votes
2answers
12k views

SSIS SQL Server 2008 errors

I just want to know how to import data from source to SQL Server 2008 database using SSIS. I have a simple txt file (actually it's from this blog : http://blog.sqlauthority.com/2008/02/06/sql-server-...
0
votes
1answer
47 views

Copying a subscribed database to the publisher server

I have two servers. I have installed a merge replication in the publisher (Server A) and I have added two subscriptions with the same database name. - One on the publisher itself and the seconde one ...