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

learn more… | top users | synonyms (1)

1
vote
1answer
640 views

ActiveX script throwing runtime error inside DTS package

I am a DBA. We have migrated SQL 2000 databases + DTS packages to SQL 2008. One of the DTS packages runs an ActiveX script to open an Excel file (which has runs macros when you open it). Coding ...
2
votes
1answer
448 views

SQL Server 2008 and Management Studio - Intellisense is Patchy

I'm working off an SQL 2008 instance that sits on our intranet. All is fine except that sometimes intellisense is there and working and sometimes it isn't. Its a pretty fast network so I don't imagine ...
1
vote
1answer
638 views

New SQL instance and migration

What are the best practices of creating new SQL Server instance on SQL Server Clustering 2008 R2 and migrating a database with high transactions?
1
vote
1answer
4k views

tsql to find users and database level permissions mapped to a certificate

On SQL 2005 and above, how can I find users and database level permissions mapped to a certificate? Story behing this: I copied a database from SQL 2005 to SQL 2008. It had a certificate inside the ...
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 ...
1
vote
3answers
2k views

How to choose the last recorded added without sorting

I've got this simple table: RecordID (int PK), UserID (int FK), DTime (datetime), Length (int) I need to run a simple query: SELECT TOP(1) * FROM uTable WHERE UserID = @userid ORDER BY DTime DESC ...
14
votes
3answers
692 views

How to store 'n' days of web server logs in Sql Server?

For faster reporting and performance analysis, we want to insert our web server logs into Sql Server. This will allow us to see traffic patterns, issues, slowdowns in near real-time. We have a daemon ...
4
votes
1answer
978 views

SQL 2008: agent job error in opening excel files using DTS package

I have a SQL 2008 server (SQL2008S1) with Excel installed on it. I had to migrate databases and DTS packages from a SQL 2000 server (SQL2000S2) to this server. 1 DTS package is trying to access an ...
6
votes
1answer
498 views

Dependant T-SQL comparison collation

Is there any way to set the collation for a string comparison based on an independent variable or column value? Something akin to SELECT COL1, col2 FROM [TABLE] WHERE COL3 LIKE '%string%' ...
5
votes
5answers
3k views

What is the most efficient way to alter a column definition in a table with millions of rows

I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple alter table Table1 ALTER COLUMN Column1 XML NULL but it takes forever. So here are my ...
9
votes
2answers
721 views

A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box. I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
10
votes
4answers
3k views

When to use XML data type

I'm responsible for creating a database on a project. We have fields that are rarely going to have a value (1 in every 10,000 records) and I'm trying to work out the best way to store this in the ...
3
votes
1answer
653 views

SQL Server 2008 R2 Express for a single website?

Someone recently told me I could use SQL Server 2008 R2 Express edition for a single website. But don't you need the Server Agent for connections (I think. I'm trying to remember things from over ...
3
votes
2answers
3k views

How to check that stored procedure is valid

As we know, it is possible to compile and to have so stored procedures: CREATE PROC dbo.MyProc1 AS SELECT * FROM Not_Exists_Or_Removed_Table or CREATE PROC dbo.MyProc2 AS SELECT Id ...
4
votes
1answer
597 views

Impact of an Asynchronous Mirror / Full Recovery Model on Performance

For an asynchronous mirror in SQL Server 2008 R2, using the full recovery model is required. Assuming that the network and disk IO on both sides of the Mirror can keep up with the transaction logs ...
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 ...
10
votes
3answers
666 views

Executing same request from C# VS SSMS give different execution time

I have a request like this one SELECT [EstimateId], [CreationUserId], [EstimateStatusValueId], [LanguageId], [LocationId], [EstimatorUserId], [FilterUnitSystemTypeId], [EstimateNumber], ...
3
votes
1answer
705 views

Oracle query against SQL Server database giving inconsistent results

We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database. A particular table in the SQL Server database has two Varchar(36) columns. The following ...
7
votes
1answer
7k views

Breaking and Restoring a mirror

I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and ...
3
votes
1answer
5k 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 ...
2
votes
2answers
1k views

SQL 2008 R2 3rd Party Peer-to-Peer Replication, Global Site Distribution

We are looking at hosting 3 globally distributed SQL Server installations at different data centers. The intent is that Site A will serve web traffic and data for a specific region, same with Site B ...
3
votes
2answers
2k views

Select XML values when attribute involved

I have a table that stores XML values in field: OtherData. An XML value from one row: < file path="C:\WINDOWS\system32\svchost.exe" /> I would like to select all rows that have an ...
4
votes
2answers
494 views

Best way to identify orphan data?

I have a few dozen tables, that have data in them, both for the core set of data, and then the company specific data. I can never delete the core set of data, that is global for all companies. But i ...
3
votes
1answer
191 views

Can I determine if a stored procedure uses dynamic sql without parsing the definition?

Since there seems to be no way to strip the procedure definition from comments without parsing cf. this, there seem to be no reliably T-SQL query to answer this question. Or can I get this ...
3
votes
1answer
670 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 ...
2
votes
1answer
944 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 ...
7
votes
1answer
647 views

Can I prevent SQL 2008 Server Management Studio's intellisense from autocompleting with the longest word?

In SQL 2008 Server Management Studio: is there a way to keep intellisense from always picking the longest word to autocomplete? For example, I could be trying to type the word client and it'll ...
4
votes
1answer
130 views

Is there a non-DDL way to preserve back-references when staging data into production?

Let's say I've got this hypothetical schema: Source (OLTP) DB: Table Orders ------------ OrderID int IDENTITY (PK), CustomerID int NOT NULL, OrderAmount decimal NOT NULL Destination (DSS) DB: ...
7
votes
1answer
435 views

How can I rewrite for SQL Server a trigger that writing for Oracle?

How can I rewrite for SQL Server a trigger that was originally written for Oracle ? Here is my Oracle trigger code: CREATE OR REPLACE TRIGGER P000KUL_TEST BEFORE INSERT ON P000KUL REFERENCING NEW AS ...
5
votes
2answers
2k views

BULK INSERT - best usage

I need to export 150 million rows of only int/bigint columns from one SQL instance to another. I am using BCP queryout to export the rows and BULK INSERT to import the rows to another table. I split ...
4
votes
2answers
739 views

Best Setup for Database Cluster

I need to set up a good database clustering solution for a system that I am putting into production. I was hoping to get some advice from the experts as I am a primarily a developer and not a database ...
6
votes
3answers
1k views

How do I rename my local SQL development server?

I'm a dev who inherited a mostly functioning box doing most of what I need. Except for the machine name is still that of the old dev (we name it "{username}-dt" or "{username}-lt" for ease of id on ...
5
votes
2answers
549 views

Date alignment and pair matching extraction best done with TSQL or C#?

For starting with: my title sucks, so help me figure out a new one? I can't post all the SQL here (over 30k characters for the lot of it), so I stuck it on pastebin.com The problem: I get an XML ...
1
vote
3answers
257 views

Selecting an entry that spans adjacent months

So i have a database table that contains the a "event name", "start-date", "end-date" columns which is used in a calendar application im working on. The events get shown one month at a time, and that ...
3
votes
2answers
641 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
346 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
2k 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 ...
7
votes
2answers
13k views

How do I determine if a table exists in a SQL Server database in SQL Server 2008?

How do I determine if a table exists in a SQL Server database in SQL Server 2008?
1
vote
1answer
906 views

Query Linked Access database on network share via SQL Job Agent

I have an MS Access database on a shared network location which I've added as a linked server to my SQL Server instance. I'm authenticated on the network location via the server's main Windows login. ...
0
votes
0answers
164 views

What is the best query to use to monitor a SQL Server database's status?

I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state. This is the ...
4
votes
1answer
112 views

Data transformation implementation

My company's database, I am working with, makes available a data to a lot of external applications. So I need to transform the same data to a lot of dynamic views. I can see that a former database ...
5
votes
1answer
317 views

How can I benchmark the performance of UDFs to figure out which one is faster?

Basically I have two scalar UDFs that output the same data, one that uses a cursor and one that uses a recursive CTE. I want to determine which one should be used and discard the other; I'd prefer to ...
15
votes
4answers
9k views

What is the best query to use to monitor a SQL Server database's status?

I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state. This is the ...
5
votes
3answers
1k views

Remove field from composite key and collate duplicate data

I'm looking for the best way to go about changing the primary key on a table in my DB without having to completely reinsert data from files again. The modification I'm going to make is going to ...
2
votes
1answer
526 views

How to customize Deploy feature in VS2010 for SQL CLR projects?

The default deployment script for SQL CLR objects in VS2010 seems to require one to set the target database to SET TRUSTWORTHY ON and tries to enable vardecimal storage at the database level. On our ...
4
votes
2answers
159 views

Randomizing table contents and storing them back in the table

I have a table with at least a million records in it. These rows were created by a custom app that reads several SharePoint site collections and stores the item urls in the table. Now, since we read ...
24
votes
4answers
989 views

Is there a “best-practices” type process for developers to follow for database changes?

What is a good way to migrate DB changes from Development to QA to Production environments? Currently we: Script the change in a sql file and attach that to a TFS work item. The work is ...
6
votes
2answers
6k views

SQL Server 2008 log files have minimum sizes, what gives and how do I make them smaller?

Ok, to start with, I screwed up when I created the databases, using a create script roughly like so: (artificial linebreaks and names/paths for wrapping purposes) CREATE DATABASE [EXAMPLE] ON ...
4
votes
1answer
235 views

Are there tools for SQL-Server helping to add the semicolons?

Earlier versions of SQL-Server didn't recommend the use of semicolons to terminate sql-statements. Beginning with sql-server-2008 examples show, that now Microsoft recommends terminating with ...