| Author |
 Topic  |
|
|
mariposa.azul.06
Starting Member
USA
7 Posts |
 Posted - 08/13/2008 : 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)
USA
6997 Posts |
Posted - 08/13/2008 : 18:16:05
|
Did you declare @Err_severity anywhere in your code?
declare @Err_severity int
CODO ERGO SUM |
 |
|
|
mariposa.azul.06
Starting Member
USA
7 Posts |
Posted - 08/14/2008 : 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
India
47040 Posts |
Posted - 08/14/2008 : 14:20:39
|
| Add the statement given by MVJ to top among other declare statements. |
|
|
|
mariposa.azul.06
Starting Member
USA
7 Posts |
Posted - 08/14/2008 : 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
|
Edited by - mariposa.azul.06 on 08/14/2008 17:20:22 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/14/2008 : 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
USA
7 Posts |
Posted - 08/14/2008 : 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 |
Edited by - mariposa.azul.06 on 08/14/2008 18:52:27 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 08/14/2008 : 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
USA
22 Posts |
Posted - 11/14/2012 : 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
India
47040 Posts |
Posted - 11/15/2012 : 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/
|
|
|
| |
Topic |
|
|
|
declaring scalar variable
