Post reply

SQL Mail Attachments

  • December 18, 2003 at 9:37 am

    #83988

    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!

  • December 18, 2003 at 3:38 pm

    #487105

    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

  • December 19, 2003 at 1:10 am

    #487152

    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

  • December 19, 2003 at 5:00 am

    #487181

    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).

  • December 19, 2003 at 10:31 am

    #487255

    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

  • December 19, 2003 at 12:22 pm

    #487280

    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