Need Help with DBMail sending Excell attachments with query results

• etvph81

  SSC Veteran

  Points: 234

  More actions

  February 28, 2020 at 2:08 am

  #3729482

  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: 21797

  More actions

  February 28, 2020 at 10:51 am

  #3729525

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

  MVDBA

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  February 28, 2020 at 12:19 pm

  #3729541

  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: 21797

  More actions

  February 28, 2020 at 12:53 pm

  #3729551

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

  MVDBA