February 28, 2020 at 2:08 am
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
February 28, 2020 at 10:51 am
I cant see anything obvious but i'm looking at result sperator as my first guess
MVDBA
February 28, 2020 at 12:19 pm
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',
February 28, 2020 at 12:53 pm
good spot.. it's easy to lose a , or ' especially in dynamic sql or badly formatted code
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply