December 13, 2002 at 10:05 am
Hi All,
I have the following stored proc. If I fire it up it works well if there are no errors and the commit fires. THe roll back, however, gives me an error.
Here is the proc:
CREATE PROCEDURE CreateProject
@ProjectName VARCHAR(100),
@UserName VARCHAR(50)
AS
DECLARE @error AS INT
DECLARE @userid AS INT
DECLARE @New_Project_ID AS INT
SET @error = 0
IF @@TRANCOUNT = 0
BEGIN TRAN T1
SELECT @userid=UserID
FROM Users
WHERE UserName = @UserName
IF @userid IS NULL SET @error = 1
IF @Error <> 0 GOTO ERRORHANDLE
INSERT INTO Projects (ProjectName)
VALUES (@ProjectName)
SET @New_Project_ID = @@IDENTITY
SET @error = @@Error
IF @Error <> 0 GOTO ERRORHANDLE
INSERT INTO LU_User_Project (UserID, ProjectID)
VALUES (@UserID, @@IDENTITY)
ERRORHANDLE:
IF @@ERROR = 0 AND @error=0
BEGIN
COMMIT TRAN T1
RETURN @New_Project_ID
END
ELSE
BEGIN
IF @userid IS NULL
RETURN -1
ELSE
BEGIN
ROLLBACK TRAN T1
RETURN -2
END
END
Using the debugger, I get the following error when proc tries to execute the ROLLBACK:
Server: Msg 6401, Level 16, State 1, Procedure CreateProject, Line 46
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot roll back T1. No transaction or savepoint of that name was found.
@RETURN_VALUE = N/A
ANY Ideas???
Thanks,
R.
December 13, 2002 at 10:14 am
If you are using the debugger and have the check box to rollbak at the end checked then the following will not create a new transaction as @@TRANCOUNT will be 1.
IF @@TRANCOUNT = 0
BEGIN TRAN T1
Gary Johnson
DBA
Sr DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
December 13, 2002 at 10:16 am
Word to the wise!! TURN OFF AUTO_ROLLBACK if you are testing
a transaction with the debugger!! I just turned it off and it
recognised my roll back no problem. I guess that when you
have AUTO_ROLLBACK enabled, SQL Server ignores inner trans??
R.
December 13, 2002 at 10:19 am
Ahhhh !!
Thanks Gary.
quote:
If you are using the debugger and have the check box to rollbak at the end checked then the following will not create a new transaction as @@TRANCOUNT will be 1.IF @@TRANCOUNT = 0
BEGIN TRAN T1
Gary Johnson
DBA
Sr DB Engineer
December 13, 2002 at 10:26 am
not that I know much about this, but I suspect its something to with a transaction wrapped around your sp (possibly implicit).
If you check your sp interactively you should find that rolling back works ok.
You'll find that creating a transaction beforehand (manually, prior to calling the sp), then performing the call generates the same type of error you describe.
I'd suggest doing some more reading up on transactions, errors & savepoints (like I'm about too!)
Edited by - sunshinekid on 12/13/2002 12:19:07 PM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply