Avatar of redouard
redouard

asked on 

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations

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.
SQL

Avatar of undefined
Last Comment
redouard

8/22/2022 - Mon