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.
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 mastergoCREATE 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 ondeclare @Today datetime, @CrLf varchar(10), @stat_Failed tinyint, @stat_Succeeded tinyint, @stat_Retry tinyint, @stat_Canceled tinyint, @stat_In_progress tinyintdeclare @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 = 0set @stat_Succeeded = 1set @stat_Retry = 2set @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_idOPEN curFailedJobsFETCH NEXT FROM curFailedJobs INTO @job_name, @step_name, @Err_severity, @DBname, @run_datetime, @command , @ErrMessage WHILE @@fetch_status=0BEGIN -- 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 ENDCLOSE curFailedJobsDEALLOCATE curFailedJobs-- Send the Emailif (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 endgo |
|
|
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 mastergoCREATE 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] SQL2000usage: EXEC spDBA_job_notification $(ESCAPE_NONE(JOBID)) SQL2005 SP1 + 2007.1.22 Jameel Ahmed Created *********************************************************************************************************/set nocount ondeclare @Today datetime, @CrLf varchar(10), @stat_Failed tinyint, @stat_Succeeded tinyint, @stat_Retry tinyint, @stat_Canceled tinyint, @stat_In_progress tinyintdeclare @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 = 0set @stat_Succeeded = 1set @stat_Retry = 2set @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_statusfrom 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 historyselect job_id, instance_id = max(instance_id) from msdb..sysjobhistory where job_id = @job_id AND step_id =0 GROUP BY job_idUNION -- 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 stepAND sjh.run_status IN (@stat_Failed ) --filter for only failed statusORDER BY sjh.instance_idOPEN curFailedJobsFETCH NEXT FROM curFailedJobs INTO @job_name, @step_name, @Err_severity, @DBname, @run_datetime, @command , @ErrMessage WHILE @@fetch_status=0BEGIN-- Build the Email Bodyset @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 ENDCLOSE curFailedJobsDEALLOCATE curFailedJobs-- Send the Emailif (rtrim(@Body)<>'')begin set @subject =@job_name +' FAILED on \\'+@@servernameset @Body = -- 'Server= ' + @@servername + @CrLf +'Job_name = ' + @job_name + @CrLf +'--------------------------------------'+ @CrLf + @Body-- print 'Message Length = ' + convert(varchar(20),len(@Body))-- print @BodyEXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database_Mail_Profile_Name' ,@recipients=@Email_To ,@subject = @subject ,@body = @Body --SQL2005endgo |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-14 : 18:57:09
|
-- Build the Email Bodyset @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 are1. 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 thishttp://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|