April 25, 2012 at 4:29 am
Hi
The sample code is at bottom. I have introduced 2 erros in the proc. Both are level 16 errors. I was expecting that the insert statements after error generating code will either be successful for both or else will fail for both. But thats not true. the statement after 'select 1/0' succeeds bt the one after 'insert into a values('8a')' is never executed.
How does sql server decide on this?
--sample code
create table a (id int)
insert into a values(1);
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[test1]
GO
create proc test1
as
update a set id=7
select 1/0
insert into a values(8)
insert into a values('8a')
insert into a values(9)
GO
exec test1
go
select * from a
go
drop table a;
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 25, 2012 at 7:22 am
I was looking for some response here but looks like there are no specific reasons for this behaviour.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 25, 2012 at 7:27 am
S_Kumar_S (4/25/2012)
HiThe sample code is at bottom. I have introduced 2 erros in the proc. Both are level 16 errors. I was expecting that the insert statements after error generating code will either be successful for both or else will fail for both. But thats not true. the statement after 'select 1/0' succeeds bt the one after 'insert into a values('8a')' is never executed.
How does sql server decide on this?
--sample code
create table a (id int)
insert into a values(1);
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[test1]
GO
create proc test1
as
update a set id=7
select 1/0
insert into a values(8)
insert into a values('8a')
insert into a values(9)
GO
exec test1
go
select * from a
go
drop table a;
Please post the DDL for table a
Jared
CE - Microsoft
April 25, 2012 at 7:41 am
S_Kumar_S (4/25/2012)
I was looking for some response here but looks like there are no specific reasons for this behaviour.
Bumping your thread after 3 hours is entirely unnecessary. We're volunteers, we post in our spare time.
Some errors terminate the statement. Some terminate the batch, some terminate the transaction, some terminate the connection. The connection-terminating errors are sev 19 and above. The other behaviours there's no particular severity for, some errors do one, some another.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 7:41 am
SQLKnowItAll (4/25/2012)
S_Kumar_S (4/25/2012)
HiThe sample code is at bottom. I have introduced 2 erros in the proc. Both are level 16 errors. I was expecting that the insert statements after error generating code will either be successful for both or else will fail for both. But thats not true. the statement after 'select 1/0' succeeds bt the one after 'insert into a values('8a')' is never executed.
How does sql server decide on this?
--sample code
create table a (id int)
insert into a values(1);
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[test1]
GO
create proc test1
as
update a set id=7
select 1/0
insert into a values(8)
insert into a values('8a')
insert into a values(9)
GO
exec test1
go
select * from a
go
drop table a;
Please post the DDL for table a
He did.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 7:44 am
GilaMonster (4/25/2012)
SQLKnowItAll (4/25/2012)
S_Kumar_S (4/25/2012)
HiThe sample code is at bottom. I have introduced 2 erros in the proc. Both are level 16 errors. I was expecting that the insert statements after error generating code will either be successful for both or else will fail for both. But thats not true. the statement after 'select 1/0' succeeds bt the one after 'insert into a values('8a')' is never executed.
How does sql server decide on this?
--sample code
create table a (id int)
insert into a values(1);
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[test1]
GO
create proc test1
as
update a set id=7
select 1/0
insert into a values(8)
insert into a values('8a')
insert into a values(9)
GO
exec test1
go
select * from a
go
drop table a;
Please post the DDL for table a
He did.
Oops... Copy and paste error.
Jared
CE - Microsoft
April 25, 2012 at 7:51 am
So Gail, in this scenario we have 1 case that just terminates the statement (divide by zero error) and one that terminates the batch (error converting varchar to int), am I interpreting that right from the outcome? If YOU wanted to avoid one over the other, how would you determine that in error handling when they are both the same severity? By specific error number?
Jared
CE - Microsoft
April 25, 2012 at 8:03 am
So can errors with same severity level behave differently? like this case.
If yes, then it can be a problem.
GilaMonster (4/25/2012)
S_Kumar_S (4/25/2012)
I was looking for some response here but looks like there are no specific reasons for this behaviour.Bumping your thread after 3 hours is entirely unnecessary. We're volunteers, we post in our spare time.
Some errors terminate the statement. Some terminate the batch, some terminate the transaction, some terminate the connection. The connection-terminating errors are sev 19 and above. The other behaviours there's no particular severity for, some errors do one, some another.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 25, 2012 at 8:04 am
I wouldn't. I'd check my data types before inserting, ensure parameters are of the correct type so that the call to the procedure would fail not the insert and write queries that aren't susceptible to divide by zero.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 8:06 am
there's a whole list of "non-batch breaking" errors listed on the microsoft site - most of them are pretty unusual - but some are quite alarming (such as foreign key violation)
unfortunatly i can't seem to find the list..... it does exist though
MVDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply