March 3, 2010 at 10:10 am
I am executing a store proc in a sql job and would like to send an email on successfull/failure execution but would like to ignore any warnings from the execution.
EXEC Revnue.RevEmpAgg.[dbo].[usp_insertempcount] @empid,@jobrole,@startdate
EXEC msdb.dbo.sp_send_dbmail
March 3, 2010 at 10:23 am
Use a TRY ... CATCH block:
BEGIN TRY
EXEC Revnue.RevEmpAgg.[dbo].[usp_insertempcount] @empid,@jobrole,@startdate
EXEC msdb.dbo.sp_send_dbmail -- Send success mail
END TRY
BEGIN CATCH
EXEC msdb.dbo.sp_send_dbmail -- Send failure mail
END CATCH
/Markus
March 3, 2010 at 11:06 am
Can you just set ANSI_Warnings off at the top of the SP?
May 10, 2010 at 6:52 am
I am exec the above proc in a sql job using try catch block as descried above but every time i see the following error mesg in the job history and shoots out a failure email.
Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or ot... The step failed.
how do i elimnate this ?
May 13, 2010 at 1:56 pm
set Ansi_Warnings off. Look at this code. The first select returns the correct number, but also the message "Warning: Null value is eliminated by an aggregate or other SET operation", the second doesn't return the warning.
declare @t table (f1 int)
insert into @t
select 1 union all
select 2 union all
select null
select sum(f1) from @t
set Ansi_Warnings off
select sum(f1) from @t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 14, 2010 at 2:48 am
That’s what I said a few posts ago. There is a bug along these lines in SQL 2005 (below). I think there is a patch for it.
set ansi_warnings off
Declare @T1 TABLE(TestIt VARCHAR(20))
-- This works – no warning
insert into @T1(Testit) values('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
-- This doesn't work – Warning
set ansi_warnings off
insert into @T1(Testit)
exec('Select ''xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'' ')
June 3, 2010 at 7:22 am
EXEC Revnue.RevEmpAgg.[dbo].[usp_insertempcount] @empid,@jobrole,@startdate
EXEC msdb.dbo.sp_send_dbmail
I am running the above proc in a job from ServerA but the proc is on ServerB.
Apart from the warning message i am also seeing this message in the job history
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions
June 3, 2010 at 8:41 am
Ok i think i figured the problem, I do run these proc fro mdiffernt server at same times may be 2 oe 3 jobs which kick this procedure for updating a sinle table and so the error.
If so How do i manage them run succesfuly as they are automated jobs. is there a way to hold on a transaction untill the other finishes ?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply