Solved

SQL Server export/email results automatically

Posted on 2012-04-05
4
568 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

Question has a verified solution.

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

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. …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

630 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