Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

SQL Server export/email results automatically

In SQL Server 2000, I am looking to run a query automatically on a regular basis and then have the results exported to an .XLS file which then will be emailed as an attachment.
Can this be setup automatically as a JOB or Stored Procedure?  I would prefer .xls format rather than  .csv.  I need to edit the results in Excel, if it is in the .CSV format- the leading zero's get cut off when opening in Excel.  If the results cannot be emailed as an attachment but you know how to automate the export as an .XLS file (or CSV without cutting off the leading zero's when opening in Excel) - please let me know.  Thanks.
0
fjkaykr11
Asked:
fjkaykr11
  • 2
  • 2
1 Solution
 
Ryan McCauleyCommented:
I set up a process like this and it's not hard to do, but the only caveat is that you need to run it from an x86 instance of SQL Server - the x64 instance won't have access to the MSOffice driver needed to write the Excel file, where the x86 version will. In fact, we maintain an older x86 instance of SQL Server for random processes like this that need it.

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. if I remember correctly, 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 we use to do this:

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

Open in new window


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

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 = @Attachments

Open in new window

0
 
fjkaykr11Author Commented:
wow this is great. I believe we are running x86 this is a really old server running SQL Server 2000. I figured I had to use o xp_cmdshell but I wasn't sure how to incorporate the Excel part.  
I will most likely be doing this project in the furture so I won't be able to report back the results for a while.  I think this is more than enough info to get this done.  Thanks again.
0
 
fjkaykr11Author Commented:
Thanks again.
0
 
Ryan McCauleyCommented:
SQL 2000 doesn't have the sp_send_dbmail system stored procedure that you can use to send email. However, it's supported by SQL Server Express, which is free - if you don't have an x86 server around that's SQL Server 2005 or newer, you can download the package from Microsoft and set up a named instance somewhere on your network, using that to send email.

Just wanted to mention that - good luck!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now