A severe error occurred on the current command. The results, if any, should be discarded

[Paarthy]

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 as int
set @b = 1
if @a = @b
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
paarthasarathyk.blogspot.com

[Jeroen Mostert]

Post by Paarthy
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any,
should be discarded.

This indicates a bug in SQL Server; your session has crashed.

Post by Paarthy
when we use a return statement in the stored procedure.
create procedure sp_test

You shouldn't have your stored procedure names start with sp_ because this
causes potential clashes with stored procedures defined in the master
database. See http://msdn.microsoft.com/library/aa214379 for more information.

Post by Paarthy
(
@a as int
)
as
begin
begin try
begin transaction
begin
select 'i am rolling back'
rollback tran
return 1
end
end try
begin catch
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

This particular bug is fixed in SQL Server 2008, see
http://connect.microsoft.com/SQLServer/feedback/details/382985/. There is no
fix for SQL Server 2005.

You may be able to work around the issue by removing the RETURN and having
the client check for the message you get instead:

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.

Of course, if you need this to work from within another stored procedure
it's no help. You can use an output variable to pass the status instead of
the return value, that doesn't trigger the bug (but requires an interface
change).

[Dan Guzman]

Post by Paarthy
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.

To add on to Jeroen's response, the problem is not with the RETURN statement
itself, but rather the location of the RETURN within the proc. Another
workaround to avoid the "severe error" is to include a single RETURN
statement like the example below. A also added a COMMIT since I assume your
actual proc commits upon success.

Personally, I like to avoid the "Transaction count after EXECUTE indicates
that a COMMIT or ROLLBACK TRANSACTION statement is missing" message. If you
don't know whether or not the proc will be invoked from within a
transaction, you'll need to use a savepoint.

CREATE PROCEDURE dbo.usp_test
(
@a as int
)
AS
BEGIN TRY

BEGIN TRAN;
SAVE TRAN usp_test;

DECLARE @b AS int, @rc int;
SET @rc = 0;
SET @b = 1;
IF @a = @b
BEGIN
SELECT 'i am rolling back';
ROLLBACK TRAN usp_test; --rollback work done in this proc
COMMIT; --decrement @@trancount
SET @rc = 1;
END
ELSE
BEGIN
COMMIT; --decrement @@trancount
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 1
BEGIN
ROLLBACK TRAN usp_test; --rollback work done in this proc
COMMIT; --decrement @@trancount
--todo: report error
END
END CATCH
RETURN @rc;
GO
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by Paarthy
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.
create procedure sp_test
(
@a as int
)
as
begin
begin try
begin transaction
begin
select 'i am rolling back'
rollback tran
return 1
end
end try
begin catch
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
paarthasarathyk.blogspot.com

[jody aryono]

I've same problem as you are so for resolution that I Read you must set indexes as clustered instead of Non Clustered , after I Set my index to Clustered the problem went away so I hope yours too...

Post by Paarthy
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.
create procedure sp_test
(
@a as int
)
as
begin
begin try
begin transaction
begin
select 'i am rolling back'
rollback tran
return 1
end
end try
begin catch
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
paarthasarathyk.blogspot.com

Post by Jeroen Mostert
This indicates a bug in SQL Server; your session has crashed.
You should not have your stored procedure names start with sp_ because this
causes potential clashes with stored procedures defined in the master
database. See http://msdn.microsoft.com/library/aa214379 for more information.
This particular bug is fixed in SQL Server 2008, see
http://connect.microsoft.com/SQLServer/feedback/details/382985/. There is no
fix for SQL Server 2005.
You may be able to work around the issue by removing the RETURN and having
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.
Of course, if you need this to work from within another stored procedure
it is no help. You can use an output variable to pass the status instead of
the return value, that does not trigger the bug (but requires an interface
change).
--
J.

Post by Dan Guzman
To add on to Jeroen's response, the problem is not with the RETURN statement
itself, but rather the location of the RETURN within the proc. Another
workaround to avoid the "severe error" is to include a single RETURN
statement like the example below. A also added a COMMIT since I assume your
actual proc commits upon success.
Personally, I like to avoid the "Transaction count after EXECUTE indicates
that a COMMIT or ROLLBACK TRANSACTION statement is missing" message. If you
do not know whether or not the proc will be invoked from within a
transaction, you will need to use a savepoint.
CREATE PROCEDURE dbo.usp_test
(
@a as int
)
AS
BEGIN TRY
BEGIN TRAN;
SAVE TRAN usp_test;
BEGIN
SELECT 'i am rolling back';
ROLLBACK TRAN usp_test; --rollback work done in this proc
END
ELSE
BEGIN
END
END TRY
BEGIN CATCH
BEGIN
ROLLBACK TRAN usp_test; --rollback work done in this proc
--todo: report error
END
END CATCH
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
Submitted via EggHeadCafe - Software Developer Portal of Choice
Simple .NET HEX PixelColor Utility
http://www.eggheadcafe.com/tutorials/aspnet/5617a491-963d-4510-b8f1-1863ddf52bc1/simple-net-hex-pixelcolor-utility.aspx