Solved

SQL Server export/email results automatically

Posted on 2012-04-05
4
538 Views
Last Modified: 2012-04-06
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
Comment
Question by:fjkaykr11
  • 2
  • 2
4 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 37816264
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
 
LVL 3

Author Comment

by:fjkaykr11
ID: 37817984
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
 
LVL 3

Author Closing Comment

by:fjkaykr11
ID: 37817999
Thanks again.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37818324
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
back end of ssas cube views or tables? 2 27
sql query 7 35
Managing SQL log files, SQL Server 2014 6 55
Stored procedure 23 0
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now