Category: T-SQL Scripts

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 needed to write the Excel file (Microsoft.Jet.OLEDB.4.0), where the x86 version will. In fact, we maintain an older x86 instance of SQL Server for random processes like this that need it – x64 is better in almost every case, but we can’t see to completely ditch x86… 🙂

I use a stored proc that I call from a SQL Agent Job, which works great. The actual process is a bit awkward – for starters, you’ll need access to xp_cmdshell. SQL Server can’t create a new Excel file from scratch, so you have to keep a blank Excel file around, make a copy of it, and then insert into the copy to get your final result.

That said, here’s the code to generate the XLS file from your query results:

SELECT Column1, Column2, Column3, Column4
  INTO ##YourTempTable
  FROM SomeOtherTable

SET @Folder = 'C:\Temp\'
SET @DocumentBlank = 'Your Document - Blank'
SET @DocumentLong = 'Your Document - ' + CONVERT(VARCHAR(10), GETDATE(), 120)

DECLARE @CMD NVARCHAR(4000)
SET @CMD = 'COPY "' + @folder + @DocumentBlank + '.xls" "' + @Folder + @DocumentLong + '.xls"'
exec master..xp_cmdshell @CMD

-- Export the Excel sheet
SET @CMD = 'insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
	''Excel 8.0;Database=' + @Folder + @DocumentLong + '.xls;'',
	''SELECT * FROM [Sheet1$]'')
	select Column1, Column2, Column3, Column4 from ##YourTempTable'

exec sp_executesql @CMD

Once that’s exported, you can just set up the email process using sp_send_dbmail and attach the file you just generated:

DECLARE @Body VARCHAR(2000)

SET @Attachments = @Folder + @DocumentLong  + '.xls'
SET @Body = 'Your file has been generated for ' + CONVERT(VARCHAR(10), GETDATE(), 120)

exec msdb..sp_send_dbmail @profile_name = 'YourMailProfile',
	@Recipients = 'Recipients@YourDomain.biz',
	@subject = 'Your file is ready',
	@Body = @Body,
	@file_attachments = @DocumentLong

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 SQL Statement (and assuming you’re using SQL 2005+), you could either do a subquery or a WITH CTE clause and then join directly to it.

To do it, I’m using a table that has a large number of rows in it, even in an empty database (sys.columns), and then doing a cross-join to ensure that I’ll have enough rows to satisfy my entire range. This table has about 890 rows in an empty databases (or you can use the “model” database if you don’t have any user databases handy), meaning that the cross join yields about 800,000 rows – enough for almost 2200 years of days, or 100 years of hourly increments (change the “dd” in the “DATEADD” statements below to “hh” or even “mi” to do any increment of time you want).

The code:

DECLARE @StartDate DATETIME,
	    @EndDate   DATETIME

	SET @StartDate = '2012-12-01'
	SET @EndDate   = '2015-12-31'

;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
								   c1.column_id,
								   c2.object_id,
								   c2.column_id)
	     from sys.columns c1
   	    cross
	     join sys.columns c2)
SELECT DATEADD(dd, number-1, @StartDate)
  FROM numberlist
 WHERE DATEADD(dd, number-1, @StartDate) <= @EndDate

I’m using dates above, but if you wanted to use INT instead, it’s pretty straightforward:
DECLARE @Start INT,
		@End   INT

	SET @Start = 1500
	SET @End   = 64000

;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
								   c1.column_id,
								   c2.object_id,
								   c2.column_id)
	     from sys.columns c1
   	    cross
	     join sys.columns c2)
SELECT @Start + number - 1
  FROM numberlist
 WHERE @Start + number - 1 <= @End

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 anyways, as well as leaving your database prone when somebody just changes something in production, without using their development tool – ouch. Sure,  you’re protected against accidental drops, but what if somebody tweaks something in production without checking it back in? You’re hosed.

All I wanted was a simple process that would run automatically, taking periodic snapshots of the database objects and recording any changes. I decided to roll my own – it’s quick, simple, can be set up to run on a schedule, and automatically includes any new databases created on the server without any intervention.

This Stored Procedure goes through the following steps:

  1. If the Master.dbo.coSourceControl table (used to store the history) doesn’t exist, it creates it
  2. For each database on the server (so new databases are added automatically), it:
    1. Grabs the text contents of all the user objects (not flagged as “IsMsShipped”)
    2. Compares the contents of each to the last known copy (if there is one)
    3. If the object is new or has changed, add a new copy to the source control table in master
  3. Output the number of objects updated
  4. Optionally, it could email somebody to tell them about the results, but it currently does not

The history is kept in a single table – master.dbo.coSourceControl – which has the database it came from, the object_id, the object name, object contents, and the timestamp. Since it uses the object_id to track things, it will also record a name change in an object, even if the contents didn’t change.

To implement it, just grab the script and run it in the master database – it will create the stored procedure coSourceControlRefresh. That’s it – now either run it on demand, or you can schedule it. It will create the supporting table (if it’s missing) and scan every database every time it’s run. To see the history for an object, just do:

  SELECT db_name(databaseid) as [Database],
         object_name(objectid) as [Object Name],
         SourceDate,
         ObjectText
    FROM master.dbo.coSourceControl
   WHERE object_name(objectid) LIKE '%The name of some object%'
ORDER BY SourceDate DESC

Restoring a dropped or changed database object should be as simple as running the query above, grabbing the contents of ObjectText you’re interested in, and then pasting it in another window and executing it. Bam – previous version of the object restored (and this stored proc should, the next time it runs, see that you’ve altered the object and record that there’s a “new” version of it).

If you run it and like it – or don’t like it – please leave a comment to let me know – nothing expected in return, but it’s nice to know when people find it useful. I’m happy to make any enhancements you’d like to see. I hope you enjoy it and it’s able to save you from the headache of a dropped database object to which you can’t find the source!

Download the Source Control database script

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 threadsafe. For example, like this:

UPDATE TOP (1) dbo.MyQueue
   SET ClaimedBy = @Server,
       ClaimTime = @ClaimTime
OUTPUT INSERTED.QueueID,
       INSERTED.SomeData1,
       INSERTED.SomeDate2,
       INSERTED.SomeData3
  INTO #OutputTable (QueueID, Column1, Column2, Column3)
 WHERE Some Criteria...

To do this, you’ll need to create a table called #OutputTable that has the right schema, which works well if you’re returning multiple rows from your query, but is a little cumbersome to work with if you’re only doing one row at a time. If you’re only returning a single row from your UPDATE query (as I am here), there’s an alternative to OUTPUT that’s easier to use – just do variable assignment inline in the UPDATE statement! The query above becomes:

UPDATE TOP (1) dbo.MyQueue
   SET ClaimedBy = @Server,
       ClaimTime = @ClaimTime
       @QueueID = QueueID,
       @OutputVar1 = SomeData1,
       @OutputVar2 = SomeData2,
       @OutputVar3 = SomeData3
 WHERE Some Criteria...

Notice the reversed variable assignment in the second query? I’ve done away with my table, and my OUTPUT clause, and now I just have the relevant values from the row I’m interested in. Much easier to work with, and as an added bonus (though I hope you’re not in this situation), it works just fine in SQL 2000.

The caveat is that it’s only good for a single row, and it only works for UPDATE – if you’re using DELETE, you’ll still need the temp table and an OUTPUT clause.

Removing an arbitrary number of spaces from a string in SQL Server

When I was concatenating some fields to create an address, I ended up with a number of spaces in my address that were unnecessary. For example, I had this:

SELECT StreetNumber + ' ' + Direction + ' ' + StreetName + ' ' + StreetType as Address

However, when an address didn’t have a direction, I ended up with a double-space in the middle of my address, and I wanted a way to clean it up. Enter the code I found at http://www.itjungle.com/fhg/fhg101106-story02.html:
SELECT name,
       REPLACE(REPLACE(REPLACE(name,' ','<>'),'><',''),'<>',' ')
  FROM SomeTable

This shortens any run of spaces in the string into a single space – sneaky! It works in any language that supports a function like REPLACE, which scans one string for instances of a second string, and swaps them out for something else.