SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 declaring scalar variable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mariposa.azul.06
Starting Member

USA
7 Posts

Posted - 08/13/2008 :  17:54:57  Show Profile  Reply with Quote
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
7020 Posts

Posted - 08/13/2008 :  18:16:05  Show Profile  Reply with Quote
Did you declare @Err_severity anywhere in your code?
declare @Err_severity int


CODO ERGO SUM
Go to Top of Page

mariposa.azul.06
Starting Member

USA
7 Posts

Posted - 08/14/2008 :  11:25:45  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/14/2008 :  14:20:39  Show Profile  Reply with Quote
Add the statement given by MVJ to top among other declare statements.
Go to Top of Page

mariposa.azul.06
Starting Member

USA
7 Posts

Posted - 08/14/2008 :  16:35:48  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30121 Posts

Posted - 08/14/2008 :  17:36:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Post full code please.
The error is problable in a line above.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

mariposa.azul.06
Starting Member

USA
7 Posts

Posted - 08/14/2008 :  18:51:35  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/14/2008 :  18:57:09  Show Profile  Reply with Quote
-- 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
Go to Top of Page

Cowski
Starting Member

USA
29 Posts

Posted - 11/14/2012 :  17:09:18  Show Profile  Send Cowski an ICQ Message  Reply with Quote
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!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/15/2012 :  05:32:47  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000