How to export data from SQL in ad hoc way and have the data be emailed to user as csv/xls?

Question

How to export data from SQL in ad hoc way and have the data be emailed to user as csv/xls?

halifaxdal

SSCoach

Points: 19741
More actions
February 15, 2013 at 2:18 pm

#270974

This is not a pure database job, it might also involve works from front-end but I am hoping to get some idea from here. Thanks.
In my project, I need the function of user clicking on a button then a stored procedure will be invoked to do some datamining and have the data be sent to user via email.
This is not a auto job, I need this be done in a ad doc manner.
Is it possible? I know there is no problem sending data as attachment in scheduled job.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

How large of an attachment are we talking? msdb.dbo.sp_send_dbmail can send query results as a csv attachment but there are limits on the size of the attachment it will send by default that you may need to increase if you go that route. Also consider the SMTP relay may have size limits too, or the user mailbox itself on receiving the email. msdb could bloat with lots of emailing flowing too. I could see you needing to defend against impatient users spawning the same resource-intense email request multiple times as well, but other than those things I mentioned msdb.dbo.sp_send_dbmail might do what you need mostly out of the box.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

halifaxdal (2/15/2013)
This is not a pure database job, it might also involve works from front-end but I am hoping to get some idea from here. Thanks.
In my project, I need the function of user clicking on a button then a stored procedure will be invoked to do some datamining and have the data be sent to user via email.
This is not a auto job, I need this be done in a ad doc manner.
Is it possible? I know there is no problem sending data as attachment in scheduled job.
Thanks.

Do you know how to create a stored procedure that will create the CSV format?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

halifaxdal SSCoach Points: 19741 More actions · Answer 3

I know how to create the csv and send it as attachment using the sp mentioned above, I am just worrying if there might be any privilege restriction

math martinez SSC Veteran Points: 285 More actions · Answer 4

Yeah ur right you need a front end, i already encounter what you need to do and me i used vb.net. The flow of my application is this , after calling the stored procedure i put the data into data set and insert the data to excel that saved in my local drive and then i call a fuction in my application using smtp to send the files to email.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Books Online details the required permissions and if you have any doubts I am sure it is nothing running a few tests can't answer for you.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

halifaxdal SSCoach Points: 19741 More actions · Answer 6

math martinez (2/15/2013)
Yeah ur right you need a front end, i already encounter what you need to do and me i used vb.net. The flow of my application is this , after calling the stored procedure i put the data into data set and insert the data to excel that saved in my local drive and then i call a fuction in my application using smtp to send the files to email.

I think this is the right approach and I might not even need the email function since I can create the excel and let user to choose download