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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy