February 10, 2017 at 3:16 am
hello there,
I'd like to clear a situation that is driving me little bit crazy....
Immagine a procedure that looks like this, running on SQLServer 2008 R2:
create procedure dbo.TestError
as
begin
insert into dbo.SomeTable(col1) values(1);
select 1/0;
insert into dbo.SomeTable(col1) values(1);
end
go
Usually, SQLServer continues code execution even when an exception occurs.
This means, when executing this procedure, e. g. in SQL Management Studio, it inserts 2 rows into the table.
Now I call the procedure using a SQL-Job. What does happen now?
Will the execution abort when it comes to the exception? Do I have only 1 insert after the job has finished? Or do I still have 2 inserts?
Why do I ask?
I always thought, when calling a procedure via Job, execution is aborted when an exception occurs. A thing I am sure I encountered several times within the last years.
Now I have the case, and I can reproduce it with that simple example, that the procedure continues, even when called via Job.
Can any one give me a hint wether I was wrong all the years or if there is some kind of explanation?
Thank's a lot,
Wolfgang
February 10, 2017 at 3:31 am
Whether the statement, batch or connection is aborted depends on the severity of the error. I don't think it depends on whether it's called from a SQL Server Agent job. If it does, I'll have learned something new today!
John
February 10, 2017 at 6:14 am
A T-SQL step within a job will by default run under the security context of the SQLAgent service account or alternately under context of a proxy account that you have specified in the step configuration. That's one significant difference between a stored procedure running by a job versus when it's called by a user account or application service account. That doesn't necessarily explain what you're seeing but just keep that in mind.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 10, 2017 at 6:26 am
Also, the session settings XACT_ABORT or ARITHABORT can influence behavior of stored procedure to an error.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply