I’ll be presenting a pair of lightning sessions at SQL Saturday in Phoenix, AZ, on April 28th – if you’re in the area and up for a day of free training on everything database-related, as well as some networking time with your fellow DBAs and Developers, please join us! Here’s the summary for my two …
Category Archive: SQL Server
SQL Server and T-SQL Development
Apr 06
Export from SQL Server to XLS and email results
Sometimes you want to take some query results and export them directly to an XLS file – here’s how you can set that up in SQL Server. The biggest caveat is that you need to run it from an x86 instance of SQL Server – the x64 instance won’t have access to the Jet driver …
Mar 15
Return a list of all dates between a start and end date
In some situations, you’ll need to work with a list of numbers or dates that are between some start or end, and you don’t have a complete list handy for joining to. Using the script below, you can create one to use in your query – if you wanted to use this in-line in another …
Feb 22
Failing maintenance plan on SQL Server 2005 when databases are offline
My backup/maintenance plan recently started failing with the following (not so helpful) error message: It turns out the error was as a result of one of my databases being offline – the plan was set to work on all databases (the default), but there’s a checkbox that tells SQL Server to skip databases that aren’t …
Dec 17
Changing SQL Server data file locations in clustered instance using service SIDs
Recently I added a second LUN to a clustered instance of SQL Server to isolate the logs files (and another for the TempDB) and I mounted them to an empty NTFS folder as I’ve done before, but when I started SQL Server, I recieved the following error: The error means that SQL Server doesn’t have …
Nov 30
Clustering walkthrough for SQL Server 2008 on Windows 2008
I recently stumbled across a great walk-through for clustering SQL Server on newer versions of Windows. It’s really thorough – everything from setting up iSCSI (in this case, to simulate a shared disk when it’s physically attached to one node – not ideal, but lets you test the walk-through), adding the required server roles, preparing …
Sep 08
Slow SQL Server queries over a linked server from Windows Server 2008
We recently ran into an issue where linked server queries where sometimes taking significantly longer than they should have – queries that executed in under a second on the source server took two minutes when executed from a remote SQL Server over a linked server connection. We assumed it was something to do with our …
Jun 15
Roll your own lightweight SQL Server source control
I’ve wanted to implement some kind of source control on my SQL Servers before, but the only product available at the moment is Red-Gate’s SQL Source Control, and I didn’t need all the functionality it offered (or want to pay for it). Also, it relies on developers checking-in their changes, and that’s prone to forgetfulness …
May 12
Error TF255271 while upgrading TFS 2005 -> 2010
When upgrading TFS 2005 to TFS 2010 (using these instructions) and it worked great on my test computer, but when I went to migrate the production server, I received the following error: Warning Message: [2011-05-12 20:12:14Z] Servicing step Register Integration Database failed. (ServicingOperation: UpgradePreTfs2010Databases; Step group: AttachPreTFS2010Databases.TfsFramework) Warning Message: TF255271: The team project collection could …
Feb 21
Lightweight, single-row alternative to OUTPUT clause in T-SQL
SQL Server 2005 adds the option for an OUTPUT clause in your query to act upon table rows and return the old and new values. When I’ve done queuing in the past, I’ve used the clause to mark a row as processing and return the value, all in a single operation, so it’s lightweight and …
#1
#2
#3
Questions Answered
Overall Points
