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 ...
5
votes
4answers
2k 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 ...
2
votes
1answer
863 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: ...
2
votes
2answers
379 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 ...
0
votes
1answer
342 views

Why is SQL Server 2008 R2 Management Studio making me login again?

I login to SQL Server 2008 management studio each morning, it works all day just fine. When I come in the next morning and try to run a query, it asks me to login again. Where is the setting defined ...
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?
1
vote
2answers
684 views

Microsoft SQL 2008 Cluster with replication

i am in the process of setting upi a SQl Server 2008 64 bit Failover cluster. A lot of the database queries that we are running during the days are db intensive and lock a lot of the tables while ...
1
vote
2answers
394 views

SQL Server database mirroring on different servers [closed]

I have two different Windows Sever 2008R2 instance located at different places such as db1 and db2. I need to make db2 as mirror server of db1 i.e. all the database on db1 will have a mirror copy on ...
1
vote
3answers
6k 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 ...
4
votes
1answer
3k views

Problems opening standard reports

I am using MS Sql Server Mgt Studio. I select a database. I right click and select Report / Standard Reports / Index Usage Statistics I get Error: Incorrect syntax near '(' I check some of the ...
2
votes
1answer
577 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 ...
1
vote
1answer
1k views

SQL Server 2005/2008 logging connections

How can I audit SQL connections to the database from Windows credential Sql Users Is there some built-in feature where I can determine number of: all connection active connection unclosed ...
3
votes
2answers
179 views

Database Mirroring of SQL server

I have two databases that are mirrored to another server using database mirroring. The mirror server has to be down for some reason for few days. Now, the production server is having principal ...
1
vote
2answers
797 views

SQL 2008 - DBCHECK Failed

I am using SQL Server 2008 and have a Maintenance Plan setup that does a DBCHECK before the nightly backup. This has been working fine for months. It has started failing. If I run a DBCHECK on the ...
3
votes
1answer
4k views

Recommended hardware configuration Analysis and Reporting Services

What would be a good way to calculate the recommended minimum hardware configuration for a Windows 2008 R2 64bit that will be running SQL Server 2008 R2 Analysis and Reporting Services? The database ...
23
votes
4answers
9k 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 ...
5
votes
2answers
178 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 ...
2
votes
1answer
621 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 ...
-1
votes
1answer
6k 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 ...
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
3answers
133 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
2answers
343 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 ...
9
votes
2answers
2k views

How does using separate schemas affect SQL Server 2008 performance?

I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
0
votes
1answer
188 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 ...
3
votes
3answers
826 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
902 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 ...
1
vote
1answer
82 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 ...
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
2answers
6k 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 ...
4
votes
1answer
1k views

Not all members in a Windows group have permissions

I ran into a bit of an odd situation. Let's say I have a domain group [DMN\Group] which has been created as a login on my server and a user in the database. One of the members in that group is not ...
6
votes
2answers
1k views

securely exposing sql server 2008 for windows authentication

My company wants to have approximately 100 of their sales people (distributed around the country) to be able to run stored procedures from excel and return the data onto the spreadsheet. We have sql ...
1
vote
1answer
513 views

Database Tuning Advisor can't find tables across two databases

I've got a Python script which accesses two SQLServer 2008 databases. If I run it and take a trace to capture all events that occur as a result of the script, and then put that trace through the ...
25
votes
3answers
37k views

How do I assign an entire Active Directory group security access in SQL Server 2008?

I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
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 ...
6
votes
5answers
13k views

User Permissions Messed Up following a Backup -> Restore Operation

I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management ...
3
votes
2answers
11k 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 : ...
13
votes
4answers
10k views

SQL Server database on an SSD - any advantage to a separate file for every table?

I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
0
votes
1answer
46 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 ...
2
votes
2answers
5k views

Deadlock on Insert/Update

Using SQL Server 2008 R2: I'm currently attempting to track down the cause of a deadlock issue we have. Not sure where to turn. Here's the setup. TABLE Scores: id INT IDENTITY(1,1), first_name ...
1
vote
1answer
312 views

sql server 2008 email notifications

I have set up a profile and operator within SQL Server 2008. I also have an operator that is a group in MS Outlook. When I use the single user address as the notification address when a job ...
2
votes
1answer
339 views

How does SQL Server control which users get automatically added to a new database?

I re-create my Dev Database with a Visual Studio DB Project twice a week. Today I fixed my permissions to be Active Directory group based rather than giving individuals permissions. After I re-ran ...
5
votes
3answers
2k views

After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column

I have two tables Location Master ([LocatioNo],[LocationName],[Description]) Location Data ([LocationNo],[LocationCode],[DDate],[BillNo],[Model],[Quantity],[SNo]) and a view involving the 2 ...
4
votes
1answer
120 views

Should the host machine always be restarted after upgrading an SQL Server 2008 instance?

My organization recently upgraded all its production SQL Server 2008 instances to Service Pack 3, Cumulative Update 1. It's my responsibility to make sure that our development database environments ...
7
votes
1answer
140 views

Insert triggers to eliminate sensitive information

A vendor system (i.e. outside of my control) is inserting a row into a table into a database that I do control. One column in this insert will have sensitive information that I do not want in my ...
0
votes
1answer
118 views

sql server maintence plans. What account to use?

When setting up a maintenance plan specifically for backups, what account should you use as the owner. Currently we use the domain account that sql server runs as. Is this best practice or should we ...
3
votes
2answers
440 views

Archive a single giant table from a SQL 2008 database - filegroup backup?

I have an extremely large table (400GB data size, inside a 2.5TB database) that is no longer needed. Before we drop it, however, our client contract says we need to archive it to tape in case in needs ...
1
vote
1answer
417 views

Design SQL Server 2008 new install … which collation?

A customer has a SQL Server 2005 instance, collation SQL_Latin1_General_CP1_CI_AS, with 8 databases ... for a new application, we have been asked to install a SQL Server 2008 with collation ...
17
votes
1answer
24k views

Table-Valued Parameter as Output parameter for stored procedure

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...
2
votes
1answer
374 views

Mirroring multiple source databases to a single destination in SQL Server 2008

How can we maintain multiple source databases data in a single destination server database in SQL Server 2008? They all are at different networks, if that makes any difference.
1
vote
1answer
890 views

Create Index with a WHERE clause

I've read the documentation on the MSDN, but it doesn't answer what I'm after. If I create an index on specified columns using a WHERE clause for certain IDs, this will limit the index to those IDs ...