When I run the below sql script, I get the following error message:
"Server: Msg 141, Level 15, State 1, Line 37
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations"
Declare @Job_Name varchar(255),
@Step_Name varchar(255),
@ServerName varchar(255),
@SQL nvarchar(4000),
@ErrMsg varchar(4000),
@execstring VARCHAR(8000),
@email VARCHAR(8000)
Set @ServerName = (select @@servername)
set @email = 'ruben.edouard@weil.com'
Set @Job_Name = (select top 1 ''''+ sysjobs.name +''''AS Job_Name from msdb..sysjobs INNER JOIN msdb..sysjobhistory ON sysjobs.job_id = sysjobhistory.job_id WHERE (sysjobhistory.run_status = 0)
And sql_severity = '16' and sql_message_id = '17967' And sysjobhistory.step_name <> '(Job outcome)'
And sysjobhistory.run_date = CONVERT(CHAR(12), GETDATE(),112) ORDER BY sysjobhistory.run_time DESC)
Set @Step_Name = (select top 1''''+ sysjobhistory.step_name +'''' AS Step_Name from msdb..sysjobsteps INNER JOIN msdb..sysjobhistory ON sysjobsteps.job_id = sysjobhistory.job_id WHERE (sysjobhistory.run_status = 0)
And sql_severity = '16' and sql_message_id = '17967' And sysjobhistory.step_name <> '(Job outcome)'
And sysjobhistory.run_date = CONVERT(CHAR(12), GETDATE(),112)ORDER BY sysjobhistory.run_time DESC)
Set @SQL = 'Error messages in job'+ space(1) + @Job_Name + space(1)+ 'on step' + space(1)+ @Step_Name + space(1)+ 'on' + space(1) + @ServerName
select @sql
Set @ErrMsg = (select top 1''''+ sysjobhistory.message +'''' AS Step_Name from msdb..sysjobsteps INNER JOIN msdb..sysjobhistory ON sysjobsteps.job_id = sysjobhistory.job_id WHERE (sysjobhistory.run_status = 0)
And sql_severity = '16' and sql_message_id = '17967' And sysjobhistory.step_name <> '(Job outcome)'
And sysjobhistory.run_date = CONVERT(CHAR(12), GETDATE(),112)ORDER BY sysjobhistory.run_time DESC)
SELECT @ErrMsg,
@execstring = 'master.dbo.xp_sendmail
@recipients = '''+ltrim(rtrim(@email))+''',
@message = '''+@sql+''',
@query = '''+@ErrMsg+''',
@Subject = '''+@Step_Name+''',
@width = 500,
@no_header = True;'
EXEC(@execstring)
GO
What am I doing wrong and how can I get pass it? Any assistance is helpful.