Need Help with DBMail sending Excell attachments with query results

  • etvph81

    SSC Veteran

    Points: 234

    Hello All,

    I'm having trouble trying to get this to work:

    This is the error I get when I run this script can anyone point anything out that I might have missed or re write it for me so it does work also what is the best way to do this so it does it weekly in a SQL Job?

    Error I get when I run the code below it:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '='.

     

     

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'WESA DC Weekly',

    @recipients = 'edward.vahovick@example.com',

    @query ='select nStartLocationID,nEndLocationID,nDonorCount,tEffectiveDate,cChangedBy

    from Data_Org_Location_Donations

    where nOrganizationID = @nORGID

    and nDonationTypeID = @nDonationTypeID

    and nactive=1

    and tEffectiveDate between @startdate and @enddate

    ORDER BY tEffectiveDate'

    @subject = 'Test email',

    @attach_query_result_as_file = 1,

    @query_attachment_filename='filename.xls',

    @query_result_separator=' ' -- tab

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    I cant see anything obvious but i'm looking at result sperator as my first guess

    MVDBA

  • Jonathan AC Roberts

    SSCoach

    Points: 17204

    Are you sure you've pasted in the query you are running? I tried it and got:

    Msg 102, Level 15, State 1, Line 21
    Incorrect syntax near '@subject'.

    Which is due to a missing comma to enclose the @query string:

    '
    ORDER BY tEffectiveDate', /*comma was missing */
    @subject = 'Test email',

     

     

     

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    good spot.. it's easy to lose a , or ' especially in dynamic sql or badly formatted code

    MVDBA

Viewing 4 posts - 1 through 4 (of 4 total)

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