Make VB wait for SQL & Rollback 2

[RobHVB6Sql]

Hi, this is my first attempt at rollback.
(Using VB6 and TransactSQL)

I have a stored procedure 'spLoopProcWithRB'
which creates two rows for each client listed in a working table
(normally a couple of hundred).

This works for a small number of clients, ie Ok for 100 but does not create all rows for say 150+.

My questions:
1) Is there a way to get VB to wait until SQL is finished?
2) 'spLoopProcWithRB' is not reporting errors.
Is there a better way to write /code this?
3) Is this a better method than my code?
{

http://www.tek-tips.com/viewthread.cfm?qid=927099}

Reference = 'spLoopProcWithRB'

/* Reusable procedure allowing looping through           */
/* a working table to run another stored procedure.      */
/* Also has rollback.                                    */

if exists (select * from sysobjects where id = object_id('spLoopProcWithRB') and sysstat & 0xf = 4)
   drop procedure spLoopProcWithRB

CREATE PROCEDURE spLoopProcWithRB
(
   @UserID         VARCHAR (20),
   @ExecuteString1 VARCHAR (300),
   @ExecuteString2 VARCHAR (300),
   @Recipient      VARCHAR (20)
)
AS

-- declare variables for use in the proc
DECLARE @PersonID     VARCHAR(20)	 -- PersonID is the identity of the person currently being processed
DECLARE @ContactID    VARCHAR(20)	 -- ContactID is the identity of the new row inserted here 
DECLARE @ExecSQL      VARCHAR(6000)      -- constructed SQL to run

-- declare a cursor to step through the prepared working table
-- only select rows where processing indictor is null just in case we want to make this
-- proc 'restartable from error'
DECLARE curClient INSENSITIVE CURSOR FOR
    SELECT PersonID
    FROM   dbo.tblTempClientList
    WHERE  ProcessedWhen IS NULL
      AND  UserID = @UserID

-- open cursor, fetch first row
OPEN curClient
FETCH NEXT FROM curClient INTO @PersonID

--for each row returned in the cursor... 
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- start a transaction
   BEGIN TRANSACTION

   -- prepare & execute some dynamically constructed SQL
   SELECT @ExecSQL = @ExecuteString1 + @PersonID + @ExecuteString2
   EXECUTE (@ExecSQL)

   -- if there was an error...
   IF @@ERROR <> 0 
   BEGIN
      PRINT 'Postal contact record error. @ExecSQL: ' + @ExecSQL + ' PersonID:' + @PersonID --added by RH 25/10/04
      ROLLBACK TRANSACTION
      RETURN 1
   END
   ELSE
   BEGIN
      /* save the generated ContactID to return */
      SELECT @ContactID = CONVERT(VARCHAR, @@IDENTITY)
   END

   --create a record for the Recipient -a mandatory field 
   SELECT @ExecSQL = 'INSERT INTO tblPostalContactDetails (ContactID, Type, ReferenceCode) ' +
                     'VALUES (' + @ContactID + ', "Postal Recipient", ' + @Recipient + ')'
   EXECUTE (@ExecSQL)

   -- if there was an error...
   IF @@ERROR <> 0 BEGIN
      PRINT 'Recipient record error. @ExecSQL: ' + @ExecSQL + ' PersonID: ' + @PersonID --added by RH 25/10/04
      ROLLBACK TRANSACTION
      RETURN 1
   END

   -- finish the transaction
   COMMIT TRANSACTION

   -- get next row
   FETCH NEXT FROM curClient INTO @PersonID
END

-- finished, clean up cursor
CLOSE curClient
DEALLOCATE curClient
GO

Example of what is sent to spLoopProcWithRB:

spLoopProcWithRB 'he31195', 
'spPostalContactUpdate 0, ', 
', 123, "27-Oct-2004", "33", "Automatically inserted by Enigma on 27-Oct-2004."', 
'1'

Thanks in advance.

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[donutman]

Your code should handle the multiple inserts as might be required for a given @UserID. In order to get the identity values for them you need to use the field "Created" to filter out those newly entered records.
Your code "User + ', ' + CONVERT(varchar,getdate())" should be replaced by something constant and unique, so if you can't change the table structure to add a better field for this purpose, then set Created = @SomeValue that includes the time of day (with seconds) and date.
After all of the records are added into tblPostalContact you can retrieve their identity fields from a Select on that table Where Created=@SomeValue. Using the information in each of those rows to add the necessary records in the 2nd table.
Does this make sense? Need more help?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

Don't loop, join.

Wow that sounds like a slogan.

Rollback rollsback every thing that happened from within the transaction. If you inserted a million rows it would roll them all back.

Test your commit rollback code by inserting a deliberate error into your second insert. Then you will know that it is set up to work correctly.

Questions about posting. See faq183-874

 

[RobHVB6Sql]

Great, only one question left now. Have implemented Karl's putting a constant into the table which works fine.

I previously tested rollback, funnily enough the same way SQLSister suggested above and rolled all back fine.

This time I'm getting this 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.

SP to date is:

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

-- finish off with any usual proc wrapup...

Rob Hasard
(VB6 /SQL 7.0)