Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Rollback error

Status
Not open for further replies.

RobHVB6Sql

Programmer
May 20, 2002
77
AU
I have setup a deliberate error in this sp to test rollback.
But it does not rollback and I'm getting an error message.
(I think the rollback and commit are in the wrong spots. Can anyone advise please).
SQL Error Message =
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

The SP to date is:
Code:
CREATE PROCEDURE spLoopProcWithRB
(  @UserID         VARCHAR (20),
   @ProviderID     INTEGER,
   @ContactDate    DATETIME,
   @Correspondence VARCHAR (20),
   @Notes          VARCHAR (255),
   @Recipient      VARCHAR (20),
   @ErrorDesc      VARCHAR (500) OUTPUT,
   @CountClients   INTEGER       OUTPUT   )
AS

-- declare variables for use in the proc
DECLARE @PersonID     VARCHAR(20)  
DECLARE @ContactID    VARCHAR(20)
DECLARE @CorrCreated  VARCHAR(80) --Constant string used to identify rows for second insert

SELECT @CountClients = 0  -- Return this to the app(VB6) as a check
SELECT @CorrCreated = 'CORRESP REPORT -Recipient Needed (' + User +  '), ' + CONVERT(varchar,getdate())  

-- start a transaction
BEGIN TRANSACTION

-- select rows from client list table
   --WORKS FINE

-- A) execute first table insert
   --WORKS FINE

-- report number of rows inserted
SELECT @CountClients = @@RowCount

-- if there was an error...
IF @@ERROR <> 0 
BEGIN
   SELECT @ErrorDesc = 'Postal contact record error. PersonID:' + CAST(@PersonID AS CHAR(4)) + "."
   ROLLBACK TRANSACTION
   RETURN 1
END

-- select rows from table
   --WORKS FINE

-- B) execute second table insert 
-- NB: deliberate error here for testing TESTING ONLY
INSERT INTO tblPostalContactDetailsXX (ContactID, Type, ReferenceCode)
SELECT pc.ContactID, 'Postal Recipient', @Recipient
FROM tblPostalContact pc
WHERE Created = @CorrCreated 

-- if there was an error...
IF @@ERROR <> 0 
BEGIN
   --hmm want to grab the last contactID used here, below may just grab the first
   SELECT @ContactID = ContactID FROM tblPostalContact WHERE Created = @CorrCreated 
   SELECT @ErrorDesc = 'Recipient record error. ContactID:' + CAST(@ContactID AS CHAR(4)) + "."
   ROLLBACK TRANSACTION
   RETURN 2
END

-- clean up Created field
   --WORKS FINE

-- finish the transaction
COMMIT TRANSACTION
GO



Rob Hasard
(VB6 /SQL 7.0)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top