SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Transactions SQL 2000
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JavierVera
Starting Member

Peru
4 Posts

Posted - 10/24/2013 :  12:15:18  Show Profile  Reply with Quote
I have the following code:

DECLARE @SERVICIO CHAR(2)

SET @SERVICIO = '11'

BEGIN TRANSACTION GET_NEXT_CORR
DECLARE @NEXT_VALUE INT


SET @NEXT_VALUE = (
SELECT TOP 1 (A.valor + 1)
FROM correlativo A
WHERE NOT EXISTS
(SELECT 1
FROM correlativo B
WHERE B.valor = (A.valor + 1))
AND A.valor NOT IN
(SELECT MAX(C.valor)
FROM correlativo C)
ORDER BY 1)

IF @NEXT_VALUE IS NULL
BEGIN
SET @NEXT_VALUE = (SELECT ISNULL(MAX(valor)+1,1) FROM correlativo)
INSERT INTO correlativo VALUES (@SERVICIO, @NEXT_VALUE)
IF (@@ERROR <> 0)
BEGIN
PRINT '1'
GOTO CORR_HANDLER
END

END
ELSE
BEGIN
INSERT INTO correlativo VALUES (@SERVICIO, @NEXT_VALUE)
IF (@@ERROR <> 0)
BEGIN
PRINT '2'
GOTO CORR_HANDLER
END
END



IF (@@ERROR <> 0)
BEGIN
PRINT '3'
GOTO CORR_HANDLER
END

COMMIT TRANSACTION GET_NEXT_CORR


CORR_HANDLER:
PRINT 'ERROR'
PRINT @@ERROR
PRINT @@TRANCOUNT
ROLLBACK TRANSACTION GET_NEXT_CORR


I would like to know why i keep getting an error that ROLLBACK has not the proper BEGIN TRAN...

What am i missing there??

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 10/24/2013 :  12:29:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
Because it's hitting the rollback tran regardless. If this is for a stored procedure, add RETURN after the COMMIT so it never hits the rollback code section. Otherwise you'll need to add an IF to the CORR_HANDLER section.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

JavierVera
Starting Member

Peru
4 Posts

Posted - 10/24/2013 :  12:48:33  Show Profile  Reply with Quote
Thanks, you made me realize how this works at least...

I wonder why keeps inserting when i get an error???? it shouldnt be on the rollback point and undo the inwsert???
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 10/24/2013 :  13:15:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are you referring to the ROLLBACK error? That error occurs after the insert/commit...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

JavierVera
Starting Member

Peru
4 Posts

Posted - 10/24/2013 :  16:58:51  Show Profile  Reply with Quote
I think i didnt make myself clear but its fine, i was referring to the atomicity of my transaction.
I ensure this function with the @@ERROR variable.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 10/24/2013 :  17:03:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
You need to store the value of @@ERROR into a variable and use that inside CORR_HANDLER as you are resetting @@ERROR with your PRINT messages after the inserts.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

JavierVera
Starting Member

Peru
4 Posts

Posted - 10/25/2013 :  15:52:18  Show Profile  Reply with Quote
yup, thanks.


heres the snippet wich i use to keep a sort of control:

DECLARE @ErrorCode INT
DECLARE @RowsAffected INT

UPDATE TABLE SET FIELD = A WHERE = B -- <<<< After each possible cause of error i update the variables value
SET @ErrorCode = @@ERROR -- <<<< After each possible cause of error i update the variables value
SET @RowsAffected = @@ROWCOUNT -- <<<< After each possible cause of error i update the variables value

IF(@ErrorCode <> 0) BEGIN
GOTO TRX_HANDLER
END
ELSE IF(@RowsAffected > 0) BEGIN
SET @piCantCerr = @piCantCerr +1
END
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000