May 22, 2010 at 8:37 am
I recieve a strange error from sql server ,
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
when we use a return statement in the stored procedure.
Below the stored procedure:
create procedure sp_test
(
@a as int
)
as
begin
begin try
begin transaction
declare @b-2 as int
set @b-2 = 1
begin
select 'i am rolling back'
rollback tran
return 1
end
end try
begin catch
if @@trancount >1
begin
rollback tran
end
end catch
end
----------------------------------------------------------------------------------------------------------
when the stored procedure is executed in the below way,
exec sp_test 1
-----------------------------------------------------------------------------------------------------------
But we get error, when we execute in the following way...
can any one please help?
begin tran
exec sp_test 1
rollback tran
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
May 22, 2010 at 9:30 am
Known issue, which Microsoft have chosen not to fix for SQL Server 2005.
The problem is fixed in SQL Server 2008.
The workaround is to omit the RETURN statement or use GOTO instead:
alter procedure sp_test
@a as int
as
begin
begin try
begin transaction
declare @b-2 as int
set @b-2 = 1
begin
select 'i am rolling back'
rollback tran
goto quit
end
end try
begin catch
if @@trancount >1
begin
rollback tran
end
end catch
end
quit:
go
The bug seems to be caused by the SQL Native Client not handling the server error correctly. You should receive these errors:
.Net SqlClient Data Provider: Msg 266, Level 16, State 2, Procedure sp_test, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
.Net SqlClient Data Provider: Msg 3903, Level 16, State 1, Line 3
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
SQL Native Client reports the first error as 'Unspecified error' which prompts the 'severe error' message in SSMS.
There is at least one Connect item for this: https://connect.microsoft.com/SQLServer/feedback/details/382985/try-catch-issue-when-include-return
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 22, 2010 at 10:15 am
Thanks Paul,
exactly what i was looking for..
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply