RobHVB6Sql
Programmer
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:
Rob Hasard
(VB6 /SQL 7.0)
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)