problem with query syntax of EXEC msdb.dbo.sp_send_dbmail
Posted on 2008-06-25
I have a script that alerts me if new records have been added to a table within the past 24 hours:
if not exists (select top 1 * from dbo.mytable where datediff(ss,UpdateDate,getdate()) < 86400)
@recipients = 'firstname.lastname@example.org',
@subject = 'Database alert: No new Data within past 24 hours!',
@importance = 'high';
While this works great, I'd really like to include the date last updated in the body of the message, something like: SELECT top 1 'last updated: ', UpdateDate from dbo.mytable order by UpdateDate desc
I tried setting this equal to the @query parameter as such:
@query = 'select top 1 'last updated: ',UpdateDate from dbo.mytable order by UpdateDate desc',
...But i see the error: 'Incorrect syntax near 'last'. I also tried escaping the ' by using two single quotes on either side of last updated (@query = 'select top 1 ''last updated: '',UpdateDate from dbo.mytable order by UpdateDate desc)
..But I see the error: 'Error formatting query, probably invalid parameters'.
Does anyone know how to fix this?
Thanks in advance.