Syntax error

Question

Post reply

Syntax error

SQLAssAS

SSCertifiable

Points: 7246
More actions
April 28, 2014 at 8:20 am

#396670

use msdb
if exists
(select sysjobs.name as [Job Name], sysjobservers.Last_run_outcome, sysjobservers.last_outcome_message, sysjobservers.last_run_date --sysjobschedules.next_run_date
from sysjobs
inner join sysjobservers on sysjobservers.job_id = sysjobs.job_id
where last_outcome_message like '%succeeded%')
--inner join sysjobschedules on sysjobservers.job_id = sysjobschedules.job_id
--order by [job name]
declare @bodytext varchar (100)
select @bodytext = (select @@servername)
EXEC msdb.dbo.sp_send_dbmail @recipients = [EMAIL@Email.com],
@subject = 'FAILED JOBS',
@Body = @bodytext,
@query = 'select sysjobs.name as [Job Name], sysjobservers.Last_run_outcome, sysjobservers.last_outcome_message, sysjobservers.last_run_date --sysjobschedules.next_run_date
from sysjobs
inner join sysjobservers on sysjobservers.job_id = sysjobs.job_id
where last_run_outcome <> '1'',
@body_format = HTML
I am setting up an email alert for when jobs fail. This is an interim measure before setting it up properly within the individual jobs. I appreciate this is not the best way to do it but it will work.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near '1'.
No matter what I put in the where statement it just does not work. But if i run the query out side of the send mail statement, it works?

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

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

a4apple SSCrazy Points: 2620 More actions · Answer 1

I think you should change the last two line to this

where last_run_outcome <> 1',

@body_format = HTML

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Lowell SSC Guru Points: 323518 More actions · Answer 2

a4apple nailed it: your inner quote needs to be double single quoted to work, or remove the single quotes to allow comparison to an integer.

:

use msdb

if exists

(select sysjobs.name as [Job Name], sysjobservers.Last_run_outcome, sysjobservers.last_outcome_message, sysjobservers.last_run_date --sysjobschedules.next_run_date

from sysjobs

inner join sysjobservers on sysjobservers.job_id = sysjobs.job_id

where last_outcome_message like '%succeeded%')

--inner join sysjobschedules on sysjobservers.job_id = sysjobschedules.job_id

--order by [job name]

declare @bodytext varchar (100)

select @bodytext = (select @@servername)

EXEC msdb.dbo.sp_send_dbmail @recipients = [EMAIL@Email.com],

@subject = 'FAILED JOBS',

@Body = @bodytext,

@query = 'select sysjobs.name as [Job Name], sysjobservers.Last_run_outcome, sysjobservers.last_outcome_message, sysjobservers.last_run_date --sysjobschedules.next_run_date

from sysjobs

inner join sysjobservers on sysjobservers.job_id = sysjobs.job_id

where last_run_outcome <> ''1''',

@body_format = HTML

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 3

SQLAssAS

SSCertifiable

Points: 7246

April 28, 2014 at 8:34 am

#1709193

Ah .. so simple.

Thanks.. appreciated.