This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (1)

2
votes
4answers
9k views

No Maintenance Plans Folder in SQL Studio 2008 R2 [closed]

A client has SQL Server 2008 R2 installed on a Windows 2003 R2 32-bit server. Under Management in SQL Studio 2008 R2, there is no maintenance plans folder. It is not under Management --> Legacy ...
2
votes
4answers
3k views

SQL Server: cold backup, shutting down services?

One of our collagues told me that they are doing "cold" backups for SQL Server so that they are shutting down services and then taking a backup somehow. I think this is all wrong! Am I right? I think ...
3
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 ...
9
votes
5answers
11k views

Save Results as CSV *with* headers in SSMS

With SQL Server 2008 R2, when I save the results as a CSV there are no headers. I can work around this by copying and pasting with the "Copy with Headers" , or just grab the headers and paste them in ...
6
votes
1answer
4k views

Add DEFAULT to existing column

In Microsoft SQL server, how can I add/change the default on an existing table column using T-SQL?
3
votes
1answer
625 views

Query Parallelism

I have a long running query that behaves differently between two different machines. I have an 8 core machine running Microsoft SQL Server Developer Edition (64-bit) that does not use parallelism and ...
3
votes
3answers
423 views

How to determinate in a trigger direct inserts and inserts via stored procedure

TableX can be modified in two ways: client does "direct" inserts client uses stored procedure to inserts records How to determinate the way of client's call (direct or stored proc) in a trigger of ...
6
votes
2answers
2k views

Is it possible to get execution call stack in a trigger?

I have 10 stored procedures and each of them does INSERTs into one tableX. Is it possible in a trigger body of tableX to get what object causes modification of tableX (stored proc1 or sp2 or....) ? ...
13
votes
3answers
8k 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 ...
6
votes
5answers
6k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...
3
votes
1answer
514 views

SQL 2008 Server agent cant see perfmon counters

I am trying to create a new SQL server agent alert to capture "SQLServer:General Statistics|Processes Blocked||>|5' but it looks like alert creation GUI is not able to see any perfmon counters as in ...
3
votes
1answer
83 views

How do you move a Database from one server to the other using MS-SQL-SMS

I need to move a database, but I want to make sure I do it safely and with all triggers, store proc and functions. I was think to simply detach the database from my server using MS-SQL-SMS and move ...
12
votes
4answers
9k views

SQL Server statements intermittently slow on SQL Server 2008 R2

On one of our customers, we've been having some performance issues on our application. It's a .NET 3.5 web app that is consuming and updating data on a SQL Server database. Currently our production ...
1
vote
1answer
237 views

SQL Server Schema Security

My company uses a multi-tenant database (one database, all tables have a customerID field). We are in the process of setting up a data warehouse and giving clients direct access. Our initial ...
2
votes
1answer
693 views

Change Tracking Retention from a backup?

I have a backup of a SQL Server 2008 database with change tracking enabled on it. The retention period is 2 days. AutoCleanUp is set to true. The database backup is 5 days old and has 2 days worth of ...
8
votes
2answers
3k 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 ...
6
votes
6answers
3k views

On DB2, who updated a record and what did the record look like before the update?

(SQL Server 2008 R2 Standard, database under full recovery) I have a table with fields id, firstname, lastname. A statement is executed: insert into dbo.sometable (id, firstname, lastname) values ...
3
votes
2answers
372 views

Windows Storage Server, SQL Server, regular files — means to protect from insider threats?

This is my first post here, and I am by no means qualified as a Windows Server or Storage Server 2008 or SQL Server 2008 administrator. I am mostly a Unix guy, however tasked with a job to investigate ...
1
vote
1answer
396 views

Ms Access queries equivalent in Sql Server

Queries in MS Access can act like views do in oracle in that you can query against them. Example: if you have a query in access called allProducts, you can also do SELECT * from allProducts; My ...
7
votes
3answers
3k views

Chaining SQL Server Agent Jobs

I have several work packages for each I have setup up a job in sql server agent. Sometimes I should run all together. What is the best way to run them all in a given sequence? I'm a bit surprised that ...
4
votes
1answer
353 views

How can a raised alert fail to notify an operator via e-mail?

SQL Server 2008: A message with @msgnum = 75000 and @severity = 16 was added to the SQL instance. An alert was added that is raised when error number 75000 is raised. The alert is enabled and an ...
3
votes
1answer
7k views

CONTAINS function in MS SQL 2008 R2

I try the following SQL query : SELECT FirstName, SecondName FROM Persons WHERE CONTAINS(FirstName, 'Mat') and I got this error : Cannot use a CONTAINS or FREETEXT predicate on table or ...
3
votes
1answer
1k views

What does “slot 80 for LOB data type node does not exist” error in sql server mean?

When I modify a table in SQL Server 2008 I get the following error: The Database ID 29, Page (1:235225), slot 80 for LOB data type node does not exist. This is usually caused by transactions ...
8
votes
2answers
978 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 ...
0
votes
1answer
418 views

Performance problem caused by joining with the result of a scalar function?

In the following example the performance of the first version using only one SQL query is very bad. The problem is the last join. When I store the result of the left side into a temporary table and ...
1
vote
1answer
1k views

Recovering from: A transport-level error has occurred when sending the request to the server in SSMS

Every now and than I loss the connection in SSMS and get the message Msg 121, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP ...
3
votes
2answers
719 views

A query submitted from different applications has differing DOP

I have a query which selects from multiple views and is fairly I/O heavy. If I execute this query using Management Studio it uses parallelism across most of the 16 CPUs and completes in under 10 ...
0
votes
1answer
331 views

Date Format in Sql Server

I am importing data from another sources, which has multiple columns but I am facing problem in date column like I have multiple format in this column like: dd/mm/yyyy mm/dd/yyyy mmm-yyyy Can you ...
5
votes
3answers
864 views

SQL Server database file fragmentation

I have inherited a system where the previous DBA added 7 data files to the PRIMARY filegroup (8MB initial size) and left the AUTOGROW option at 8MB. What I have now is a set of eight files each about ...
4
votes
2answers
1k views

Why did my UPDATE statement not complete?

Running SQL Server 2008; Mgmt Studio 2008. I am working with a MS SQL job that we have created for our MS CRM 4.0 implementation. For anyone familiar with CRM, this job purges the aSyncOperationBase ...
2
votes
2answers
301 views

Search on part of a column in a SQL Server cross-database view

I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views ...
6
votes
1answer
5k views

Restore encrypted database to another server

I'm using a product which runs on SQLServer 2008. Understandably the company supplying it don't offer SQLServer support. When I installed the product I specified a password to encrypt the database. ...
1
vote
1answer
236 views

Table Partitioning. What is the correct process for deleting .ndf and .ldf files?

Please go easy it’s a steep learn curve.. OK steps done so far Create files, Create file groups, Create partition function, Create partition scheme, Partition table associate files with filegroups ...
7
votes
1answer
7k views

Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts?

During investigation of a slow query it appeared that the execution plan was exceptionally suboptimal (A nested loop performing 9 million executions of a seek where the estimated number of executions ...
16
votes
3answers
33k views

Recommended page file size for SQL 2008R2 on Windows 2008R2

This Microsoft article - How to determine the appropriate page file size for 64-bit versions of Windows Server 2008 and or Windows 2008 R2 provides guidance for calculating the page file size for 64 ...
2
votes
5answers
2k views

Setting up a Redundant Sql Server 2008 Configuration While Retaining High Performance and Minimal Downtime

We have a situation where we are moving to a new hosting facility and are attempting to configure our new database environment. However, none of us are formal DBA's so a lot of what we are doing is ...
6
votes
1answer
856 views

Can someone explain the magic of Opendatasource/Openrowset?

When pulling information from an excel file (or ms-access DB) I commonly use something like this: SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\test.xls;Extended ...
5
votes
1answer
538 views

Indications from obscenely high Operator Costs in Query Plan?

I have a monthly ETL process that's too complicated to explain here, but basically we get a large amount of customer data, bulk load it into various tables, then run a series of build processes. The ...
8
votes
3answers
6k views

Emulate a TSQL sequence via a stored procedure

I have a requirement to create a stored procedure which emulates a TSQL sequence. That is it always gives an increasing distinct integer value on every call. In addition, if an integer is passed in ...
4
votes
1answer
548 views

How to perform the dreaded double hop with linked servers?

The situation: I have SERVER1, SERVER2, ACCESSDB on SERVER3. I have successfully created a linked server connection from SERVER1 to SERVER3's ACCESSDB. I want to be able to access the linked server ...
8
votes
5answers
3k views

Are there tools to perform data comparison between two different schemas?

We are migrating our database to a new schema but want to validate the data has been moved correctly. Traditional data comparison tools can compare two databases for differences if the schemas are ...
5
votes
2answers
544 views

SQL Server configuration / specification recommendations and advice!

I am about to deploy a new server at work as follows: Dell T610 2 x Xeon 2.53 Quad core 24 GB RAM 1 x Perc H700 RAID controller with 1GB Non Volatile memory 8 x 300GB 15k SAS Hard drives I am looking ...
3
votes
1answer
674 views

Login problem to SQL Server

I attempt to connect to SQL Server 2008,I receive the following error. Login failed for user <>. Reason: Failed to open the explicitly specified database. By the way, I have a SQL Server 2008 ...
3
votes
1answer
514 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. ...
5
votes
1answer
1k views

SQL Server 2008 Containstable generate negative rank with weighted_term

I have a table with full text search enabled on Title column. I try to make a weighted search with a containstable but i get an Arithmetic overflow for the Rank value. The query is as follow SELECT ...
2
votes
2answers
983 views

SQL 2008, how does it link Full Text Catalog to a filegroup

While creating Full Text Catalog, if "on filegroup" is specified - it creates the catalog on this file, but on MSDN I read "On Filegroup" clause has no affect?
4
votes
1answer
3k views

select permission was denied on the object 'sysjobs'

The select permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'. I have already tried to grant sqlagentoperatorrole and it didn't work. How do I resolve this error?
5
votes
1answer
1k views

Migrate DTS Jobs to 2008 Server

We have been running SQL Server 2000 for years. Finally, at long last, we have an SQL 2008 Server being shipped in. We have many DTS jobs on our SQL 2000 Server that need to be migrated to the SQL ...
4
votes
2answers
5k views

SQL Server 2008 posibilities of having views from one database in another database?

I have a vendor database from I want to extract and transform certain information. As I dont want to change the structure of the original database due to licensing I therefor wondering if I can put ...
3
votes
3answers
235 views

Checking for dupes on Compact Insert Statement

I'm not sure if that is the correct term "Compact Insert Statement" It is just what I have always heard it referred to. It goes as follows: INSERT INTO [tblUsers] ([username], [password]) ...