How do I schedule to send out query result in certain format like csv or xls? Thanks.

Question

How do I schedule to send out query result in certain format like csv or xls? Thanks.

halifaxdal

SSCoach

Points: 19741
More actions
January 12, 2010 at 9:49 am

#140563

Hi folks,
I have a client who want to use my data and he wants it in a weekly basis, the data could easily be generated using a single query. rather than create a big application to do this, I am wondering if it is possible to set up some SQL job to accomplish this?
Thank you.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

You could create a data export in SSIS (or legacy DTS) that outputs .csv data to a file share. Then schedule that inside a job to run once a week.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

You can also attach query results to a mail message with xp_sendmail.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Or you could set up Database Mail and send the data directly as a xls file using a stored proc called by a job.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

halifaxdal SSCoach Points: 19741 More actions · Answer 4

Yeah, I know this can be done by SSIS, for lots of reason, I really don't like SSIS (compare to DTS)

halifaxdal SSCoach Points: 19741 More actions · Answer 5

lmu92 (1/12/2010)
Or you could set up Database Mail and send the data directly as a xls file using a stored proc called by a job.

Can you elaborate this? Thanks.

LutzM SSC Guru Points: 107049 More actions · Answer 6

It depends on where you want to start...

Assuming, you need to start from the very beginning, I'd recommend the following article:

http://www.sqlservercentral.com/articles/Administration/introtodatabasemailinsql2005/2197/

Once you succesfully send the testmail you should check "sp_send_dbmail (Transact-SQL)" in BOL (Books Online).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

halifaxdal SSCoach Points: 19741 More actions · Answer 7

lmu92 (1/12/2010)
It depends on where you want to start...
Assuming, you need to start from the very beginning, I'd recommend the following article:
http://www.sqlservercentral.com/articles/Administration/introtodatabasemailinsql2005/2197/
Once you succesfully send the testmail you should check "sp_send_dbmail (Transact-SQL)" in BOL (Books Online).

Thanks, I have no problem sending email, but just wondering how to attach a query result as xls (or csv, text) format. Can you provide some sample code? Thanks lots.

halifaxdal SSCoach Points: 19741 More actions · Answer 8

Never mind, I know how to do it now. Thank you again for your help.

LutzM SSC Guru Points: 107049 More actions · Answer 9

Based on BOL, extended by some more parameter (untested, but should work):

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'AdventureWorks Administrator',

@recipients = 'danw@Adventure-Works.com',

@query = 'SELECT COUNT(*) FROM AdventureWorks.Production.WorkOrder

WHERE DueDate > ''2004-04-30''

AND DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,

@subject = 'Work Order Count',

@attach_query_result_as_file = 1 ,

@query_result_separator = '' ,--Tab

@query_attachment_filename = 'MyFile.xls'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

- Win. SSCertifiable Points: 6790 More actions · Answer 10

I tried this and its working fine.

Thanks for sharing.

But the result in the file is in improper manner.

Cheers,
- Win.

" Have a great day "

- Win. SSCertifiable Points: 6790 More actions · Answer 11

Hi,

adding a new bit here, can we make this to work for GMail or Yahoo mails ?

any thoughts ?

as i tried in the past but never resolved.

Thanks in advance.

Cheers,
- Win.

" Have a great day "

LutzM SSC Guru Points: 107049 More actions · Answer 12

You might want to check this thread.

Seemes like someone finally found a way to do it. I haven't tested it myself though...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]