declaring scalar variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

declaring scalar variable

Author

Topic

mariposa.azul.06
Starting Member

7 Posts

Posted - 2008-08-13 : 17:54:57

I have a piece of code that is giving me this error and i need help trying to figure how to declare the scalar variable:

Must declare the scalar variable "@Err_severity".

here is my code:
if (@Err_severity<>0)
set @Body = @Body +
'Severity = ' + convert(varchar(10),@Err_severity) + @CrLf

set @Body = @Body +
'Error = ' + ISNULL(@ErrMessage,'') + @CrLf + @CrLf +
'Command = ' + ISNULL(@command,'') + @CrLf

any suggestions will be appeciated

--Mariposa

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-13 : 18:16:05

Did you declare @Err_severity anywhere in your code?

declare @Err_severity int

CODO ERGO SUM

mariposa.azul.06
Starting Member

7 Posts

Posted - 2008-08-14 : 11:25:45

I got the code from google -- not my original code...
I am new to this -- here is the entire code -- I don't know where I need to declare the variable...

USE master
go
CREATE PROCEDURE [dbo].[spDBA_job_notification] @job_id uniqueidentifier as
/*********************************************************************************************************
Purpose: SQL Job Agent does not send the error messages on failure,
so this procedure queries msdb to for the error message and sends an email.

usage: EXEC spDBA_job_notification [JOBID] SQL2000
usage: EXEC spDBA_job_notification $(ESCAPE_NONE(JOBID)) SQL2005 SP1 +

2007.1.22 Jameel Ahmed Created
*********************************************************************************************************/
set nocount on
declare @Today datetime, @CrLf varchar(10), @stat_Failed tinyint, @stat_Succeeded tinyint, @stat_Retry tinyint, @stat_Canceled tinyint, @stat_In_progress tinyint
declare @Email_To nvarchar(100), @Email_From nvarchar(50), @subject varchar(200), @Body varchar(8000)
declare @job_name sysname, @step_name sysname, @Err_severity int, @run_datetime datetime, @DBname sysname
,@command varchar(3200), @ErrMessage varchar(1024)
set @Body = ''
set @CrLf = char(10)--+char(13) --carriage return & line feed
--constants for Job Status execution:
set @stat_Failed = 0
set @stat_Succeeded = 1
set @stat_Retry = 2
set @stat_Canceled = 3
set @stat_In_progress = 4
set @Today = getdate()

set @Email_TO = 'sqldba@ibsa.com'
set @Email_From = usatxmdrr11a + 'sqldba@ibsa.com'

DECLARE curFailedJobs CURSOR READ_ONLY FOR

select sj.name, sjh.step_name, sjh.sql_severity, sjs.database_name ,run_datetime= convert(datetime, left( run_date ,4)+'/'+substring( run_date ,5,2)+'/'+right( run_date ,2)+' '+ left( run_time ,2)+':'+substring( run_time ,3,2)+':'+right( run_time ,2) )
,sjs.command, sjh.message --,sjh.run_status

from msdb..sysjobs sj

join (select instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,message,run_status,run_duration,operator_id_emailed,operator_id_netsent,operator_id_paged,retries_attempted,server
,run_date= convert(varchar(8), run_date )
,run_time= case when len(convert(varchar(8), run_time )) = 5 then '0' + convert(varchar(8), run_time ) else convert(varchar(8), run_time ) end
from msdb..sysjobhistory) sjh on sj.job_id=sjh.job_id

join msdb..sysjobsteps sjs on sjs.job_id=sjh.job_id AND sjs.step_id=sjh.step_id

-- sjh_Min contains the most recent instance_id (an identity column) from where we should start checking for any failed status records.
join (
-- to account for when there is are multiple log history
select job_id, instance_id = max(instance_id) from msdb..sysjobhistory where job_id = @job_id AND step_id =0 GROUP BY job_id
UNION
-- to account for when you run the job for the first time, there is no history, there will not be any records where the step_id=0.
select job_id, instance_id = Min(instance_id) from msdb..sysjobhistory where job_id = @job_id AND NOT EXISTS (select * from msdb..sysjobhistory where job_id = @job_id AND step_id =0 ) GROUP BY job_id
)sjh_Min on sjh_Min.job_id =sj.job_id
AND sjh.instance_id > sjh_Min.instance_id -- we only want the most recent error message(s).

where sj.job_id = @job_id
AND sjh.step_id<>0 --exclude the job outcome step
AND sjh.run_status IN (@stat_Failed ) --filter for only failed status
ORDER BY sjh.instance_id

OPEN curFailedJobs
FETCH NEXT FROM curFailedJobs INTO @job_name, @step_name, @Err_severity, @DBname, @run_datetime, @command , @ErrMessage
WHILE @@fetch_status=0
BEGIN
-- Build the Email Body
set @Body = @Body +
'Step name= ' + @step_name + @CrLf +
'DB Name = ' + convert(varchar(50), ISNULL(@DBname,'') + @CrLf +
'Run Date = ' + convert(varchar(50),@run_datetime ) + @CrLf

if (@Err_severity<>0)
set @Body = @Body +
'Severity = ' + convert(varchar(10),@Err_severity) + @CrLf

set @Body = @Body +
'Error = ' + ISNULL(@ErrMessage,'') + @CrLf + @CrLf +
'Command = ' + ISNULL(@command,'') + @CrLf

FETCH NEXT FROM curFailedJobs INTO @job_name, @step_name, @Err_severity, @DBname, @run_datetime, @command , @ErrMessage
END
CLOSE curFailedJobs
DEALLOCATE curFailedJobs

-- Send the Email
if (rtrim(@Body)<>'')
begin
set @subject =@job_name +' FAILED on \\'+@@servername
set @Body = -- 'Server= ' + @@servername + @CrLf +
'Job_name = ' + @job_name + @CrLf +
'--------------------------------------'+ @CrLf + @Body

-- print 'Message Length = ' + convert(varchar(20),len(@Body))
-- print @Body
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database_Mail_Profile_Name' ,@recipients=@Email_To ,@subject = @subject ,@body = @Body --SQL2005

end
go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 14:20:39

Add the statement given by MVJ to top among other declare statements.

mariposa.azul.06
Starting Member

7 Posts

Posted - 2008-08-14 : 16:35:48

i now get Incorrect syntax near the keyword 'if'

if (@Err_severity<>0)
set @Body = @Body +
'Severity = ' + convert(varchar(10),@Err_severity) + @CrLf

set @Body = @Body +
'Error = ' + ISNULL(@ErrMessage,'') + @CrLf + @CrLf +
'Command = ' + ISNULL(@command,'') + @CrLf

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 17:36:20

Post full code please.
The error is problable in a line above.

E 12°55'05.25"
N 56°04'39.16"

mariposa.azul.06
Starting Member

7 Posts

Posted - 2008-08-14 : 18:51:35

USE master
go
CREATE PROCEDURE [dbo].[spDBA_job_notification] @job_id uniqueidentifier as
/*********************************************************************************************************
Purpose: SQL Job Agent does not send the error messages on failure,
so this procedure queries msdb to for the error message and sends an email.

usage: EXEC spDBA_job_notification [JOBID] SQL2000
usage: EXEC spDBA_job_notification $(ESCAPE_NONE(JOBID)) SQL2005 SP1 +

2007.1.22 Jameel Ahmed Created
*********************************************************************************************************/
set nocount on
declare @Today datetime, @CrLf varchar(10), @stat_Failed tinyint, @stat_Succeeded tinyint, @stat_Retry tinyint, @stat_Canceled tinyint, @stat_In_progress tinyint
declare @Email_To nvarchar(100), @Email_From nvarchar(50), @subject varchar(200), @Body varchar(8000)
declare @job_name sysname, @step_name sysname, @Err_severity int, @run_datetime datetime, @DBname sysname
,@command varchar(3200), @ErrMessage varchar(1024)
set @Body = ''
set @CrLf = char(10)--+char(13) --carriage return & line feed
--constants for Job Status execution:
set @stat_Failed = 0
set @stat_Succeeded = 1
set @stat_Retry = 2
set @stat_Canceled = 3
set @stat_In_progress = 4
set @Today = getdate()

set @Email_TO = 'sqldba@ibsa.com'
set @Email_From = usatxmdrr11a + 'sqldba@ibsa.com'

DECLARE curFailedJobs CURSOR READ_ONLY FOR

select sj.name, sjh.step_name, sjh.sql_severity, sjs.database_name ,run_datetime= convert(datetime, left( run_date ,4)+'/'+substring( run_date ,5,2)+'/'+right( run_date ,2)+' '+ left( run_time ,2)+':'+substring( run_time ,3,2)+':'+right( run_time ,2) )
,sjs.command, sjh.message --,sjh.run_status

from msdb..sysjobs sj

join (select instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,message,run_status,run_duration,operator_id_emailed,operator_id_netsent,operator_id_paged,retries_attempted,server
,run_date= convert(varchar(8), run_date )
,run_time= case when len(convert(varchar(8), run_time )) = 5 then '0' + convert(varchar(8), run_time ) else convert(varchar(8), run_time ) end
from msdb..sysjobhistory) sjh on sj.job_id=sjh.job_id

join msdb..sysjobsteps sjs on sjs.job_id=sjh.job_id AND sjs.step_id=sjh.step_id

-- sjh_Min contains the most recent instance_id (an identity column) from where we should start checking for any failed status records.
join (
-- to account for when there is are multiple log history
select job_id, instance_id = max(instance_id) from msdb..sysjobhistory where job_id = @job_id AND step_id =0 GROUP BY job_id
UNION
-- to account for when you run the job for the first time, there is no history, there will not be any records where the step_id=0.
select job_id, instance_id = Min(instance_id) from msdb..sysjobhistory where job_id = @job_id AND NOT EXISTS (select * from msdb..sysjobhistory where job_id = @job_id AND step_id =0 ) GROUP BY job_id
)sjh_Min on sjh_Min.job_id =sj.job_id
AND sjh.instance_id > sjh_Min.instance_id -- we only want the most recent error message(s).

where sj.job_id = @job_id
AND sjh.step_id<>0 --exclude the job outcome step
AND sjh.run_status IN (@stat_Failed ) --filter for only failed status
ORDER BY sjh.instance_id

OPEN curFailedJobs
FETCH NEXT FROM curFailedJobs INTO @job_name, @step_name, @Err_severity, @DBname, @run_datetime, @command , @ErrMessage
WHILE @@fetch_status=0
BEGIN
-- Build the Email Body
set @Body = @Body +
'Step name= ' + @step_name + @CrLf +
'DB Name = ' + convert(varchar(50), ISNULL(@DBname,'') + @CrLf +
'Run Date = ' + convert(varchar(50),@run_datetime ) + @CrLf

if (@Err_severity<>0)
set @Body = @Body +
'Severity = ' + convert(varchar(10),@Err_severity) + @CrLf

set @Body = @Body +
'Error = ' + ISNULL(@ErrMessage,'') + @CrLf + @CrLf +
'Command = ' + ISNULL(@command,'') + @CrLf

FETCH NEXT FROM curFailedJobs INTO @job_name, @step_name, @Err_severity, @DBname, @run_datetime, @command , @ErrMessage
END
CLOSE curFailedJobs
DEALLOCATE curFailedJobs

-- Send the Email
if (rtrim(@Body)<>'')
begin
set @subject =@job_name +' FAILED on \\'+@@servername
set @Body = -- 'Server= ' + @@servername + @CrLf +
'Job_name = ' + @job_name + @CrLf +
'--------------------------------------'+ @CrLf + @Body

-- print 'Message Length = ' + convert(varchar(20),len(@Body))
-- print @Body
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database_Mail_Profile_Name' ,@recipients=@Email_To ,@subject = @subject ,@body = @Body --SQL2005

end
go

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-14 : 18:57:09

-- Build the Email Body
set @Body = @Body +
'Step name= ' + @step_name + @CrLf +
'DB Name = ' + convert(varchar(50), ISNULL(@DBname,'') + @CrLf +
'Run Date = ' + convert(varchar(50),@run_datetime ) + @CrLf

Missing )
'DB Name = ' + convert(varchar(50), ISNULL(@DBname,'')) + @CrLf +

You need to learn to check syntax on your own.

CODO ERGO SUM

Cowski
Starting Member

30 Posts

Posted - 2012-11-14 : 17:09:18

I know this is a very, very, very old post but not quite sure what to do here.

I've been using this stored procedure religiously for a number of months now. Never had an issue with it until recently.

I have it sitting in between 2 stored procs that are running. One of them errors out with the following error:
"Violation of PRIMARY KEY constraint 'PK_PPPClaimsExport'. Cannot insert duplicate key in object 'dbo.PPPClaimsExport'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed."

When the procedure spDBA_Job_Notification tries to fire off that returns with the following error:
"Conversion failed when converting date and/or time from character string. [SQLSTATE 22007] (Error 241). The step failed."

I'm not sure what to do at this point. I'm not 100% sure of what is being sent to the stored proc (JobID I'm assuming?) but would like to see what is being sent there so I may troubleshoot it.

Open to suggestions.

Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-15 : 05:32:47

quote:
Originally posted by Cowski

I know this is a very, very, very old post but not quite sure what to do here.

I've been using this stored procedure religiously for a number of months now. Never had an issue with it until recently.

I have it sitting in between 2 stored procs that are running. One of them errors out with the following error:
"Violation of PRIMARY KEY constraint 'PK_PPPClaimsExport'. Cannot insert duplicate key in object 'dbo.PPPClaimsExport'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed."

When the procedure spDBA_Job_Notification tries to fire off that returns with the following error:
"Conversion failed when converting date and/or time from character string. [SQLSTATE 22007] (Error 241). The step failed."

I'm not sure what to do at this point. I'm not 100% sure of what is being sent to the stored proc (JobID I'm assuming?) but would like to see what is being sent there so I may troubleshoot it.

Open to suggestions.

Thanks!!

based on limited info you posted only things we can suggest are

1. look for definition of primary key constraint in table using sp_help 'tablename'

and see what all columns are part of PK. then make sure values you're inserting for column(s) are not one which is already existing.

2. for the second error msg, check format in which dates are passed to column or parameter in procedure. Make sure you adhere to this

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources