x

Need to Create A job which copy the result in Excel And Send it as attachement ?

Hi ALL,

Thanks For all your help and support, Today again i am stuck in a problem .

I have to create a job which execute some SQL Script and i want to copy that result on Excel or Text file and then want to sent that file as attachemnt to my Client.

Thanks

Basit Khan
more ▼

asked May 30, 2012 at 10:40 AM in Default

basit_khan gravatar image

basit_khan
80 4 4 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can use OPENROWSET to export the data to Excel

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\template.xls;', 'SELECT Col1, Col2 FROM [Sheet1$]') 
SELECT Col1, Col2 FROM tblnames
GO

The pre-requisites for this are

  • Create an excel sheet as a template and provide the sheet path and as mentioned in the query.
  • Fill the header row with the columns as provided in the query
  • If the sheet name varies from the default, then you need to change that in the query
  • You have to enable the Ad Hoc Distributed Queries option

Then you can use database mail to send this file as an attachment using SQL Agent Job. See [sp_send_dbmail][1] for help.

[1]: http://msdn.microsoft.com/en-us/library/ms190307(v=sql.105).aspx
more ▼

answered May 30, 2012 at 10:52 AM

Sacred Jewel gravatar image

Sacred Jewel
1.7k 2 4 5

(comments are locked)
10|1200 characters needed characters left

You don't mention what version of SQL Server you are using, thus I'm assuming 2005 or higher.

Just throwing this out there, you can also use the 'Export Wizard' to generate a simple package that you can then run as a job to export the data. You would then need to modify the package to include the email step in order to email the file to the recipient. Assuming that the file name doesn't change as well as the email recipient, this can be easily done in BIDS.

Even if either of those two variables need to be generated dynamically, this can be done as well with a little bit of tweaking to the package.

To start this, just right-click on the database and select Tasks-->Export Data. This will start a wizard and take you through the process of generating the SSIS package. The wizard will also allow you to schedule the package to run as a job.

I know that you stated you wanted a SQL Script to execute, however I just wanted to point out another possible solution so that you had a couple to choose from.

Hope this helps!
more ▼

answered May 30, 2012 at 02:53 PM

JohnM gravatar image

JohnM
6.9k 1 3 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x87

asked: May 30, 2012 at 10:40 AM

Seen: 3699 times

Last Updated: May 30, 2012 at 02:59 PM