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
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