Solved

SQL Server export/email results automatically

Posted on 2012-04-05
4
547 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql select record as one long string 21 22
How to create a unique rule restriction on a table for two fields 16 77
SQL view 2 26
Sql Query 6 49
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

806 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