SQL Mail Attachments
-
Does anyone know of a way to attach the results of @query as a comma delimited text file - specifically a .csv as opposed to .txt? I'd like my @query results to automatically open is excel when they are opened from outlook.
Thanks!
-
It is A LOT easier to use DTS for this!
I have try xp_sendmail using the @separator=',' and @attachments = 'file.csv' and even though it send the Formating abilities of this procedure are not good enough!!
HTH
* Noel -
We used this to attach results of @query as a csv :
exec master.dbo.xp_sendmail @recipients = 'EmailId',
@message = 'Results in CSV',
@query = 'Select au_id,au_lname,au_fname from pubs.dbo.authors',
@attach_results = 'TRUE',
@attachments = 'Authors.csv',
@separator = ',',
@width = 1024
the @width=1024 is (supposedly) needed to avoid carriage returns in the csv file...
HTH
-
I tried this and somewhere there's a post asking about a problem it produced -- it's a HUGE file then, as it had lots of padded spaces and I think unicode (a 1 meg attachment when I did it manually into Query Analyzer became 4M when emailed automatically).
DTS is straightfoward as Noeld points out, and may be a better solution in some cases, but I also found BCP to be a good solution. The file stays small and you can then directly xm_sendmail it as a separate step. We run a sql agent file with the BCP as a command step followed by a TSQL step to send it. The nice thing is it's all inside one job and no DTS package to haul around with it.
But probably 95% of the places we do this kind of thing have either DTS packages (for flat output) or VB programs (for heirarchical structure output).
-
Rachel,
I agree with Noeld and Ferguson. It is much easier with DTS. The DTS package designer has a Send Mail Task that makes this a snap.
JR
-
Thanks to everyone for your suggestions! I'm going to try the bcp suggestions first b/c franky, I need dynamic aspects to this file creation and I dislike activeX scripts. But, if that doesn't work, I guess its DTS for me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply