How do I output a query to a CSV file and attach to an email without having to save it?

I need to output the results of a query to a CSV file then send to somebody on email. I would like to schedule this automatically on a SQL Server so is it possible to create tha CSV and attach it to an email to send out?

more ▼

asked Mar 17 '10 at 01:18 PM in Default

Ian Roke gravatar image

Ian Roke
1.7k 29 33 34

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

4 answers: sort voted first

Not sure that you can do it without disk access per se.

You could try and house the results in a variable as a comma delimited string and then spill that out as your email body.

I realize this is a limited work around but it may suffice for your purposes.

more ▼

answered Mar 17 '10 at 02:17 PM

Blackhawk-17 gravatar image

11.8k 28 30 35

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

Blackhawk has a good suggestion. But if you want it to truly be an attached file, you have to generate that file somewhere.

Perhaps the sysadmin could authorize the proxy accounts for the SQL Server to write to a single directory and your job could "clean up after itself" and delete the csv file after it was sent?

On the exteme side, you could also create a ram drive and then it would never need to touch the actual hard disk. But then windows treats those as drives and SQL Server would still need permissions on it, so I suspect that will not meet your intent.

more ▼

answered Mar 17 '10 at 02:40 PM

TimothyAWiseman gravatar image

15.5k 19 22 32

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

Create a scheduled job that bcps the output to csv (using /t,), then email using sp_send_dbmail attaching the previously produced file with the @file_attachments parameter

oops - just seen you have the SSIS tag on this. Sorry! Will leave this here anyway as an indication of a non-SSIS way!

and just realised you stated 'without having to save it' - how can a csv file exist without it being saved????

more ▼

answered Mar 17 '10 at 01:52 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Sorry I need to clarify my question a bit more. I want to create the CSV file without having to physically save the file somewhere because the server at work doesn't have permissions. I was hoping to be able to save it in memory then attach it to an email.
Mar 17 '10 at 02:09 PM Ian Roke
(comments are locked)
10|1200 characters needed characters left

Yes, I've done this. Create a CLR proc that sends a system.net.mail MailMessage. The MailMessage.Attatchments can take a stream for the attachment

more ▼

answered Mar 17 '10 at 10:02 PM

Scot Hauder gravatar image

Scot Hauder
5.9k 13 15 18

Excellent. Could you provide some example code? I've never used CLR before.
Mar 18 '10 at 04:48 AM Ian Roke
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Mar 17 '10 at 01:18 PM

Seen: 4016 times

Last Updated: Mar 17 '10 at 01:18 PM