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)

 

[SonOfEmidec1100]

By default VB waits until the procedure completes or timesout.

In each of your error routines use the SQL statement RAISERROR to return some error details before the RETURN 1, this is well documented in BOL. Then in VB this will be trapped by the ON ERROR GOTO ErrorHandler.

 

[hmckillop]

As sonOfEmidec1100 says the VB should wait on the proc finishing unless you are executing specifically as asychronously.

An alternative to the raiserror would be to declare an output parameter to store you error message and instead of using Print statement, set the output variable.
If you use a ADO Command object and its associated Parameter objects then its simple to get the actual return value.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

yes there is a better method. Do not use a cursor. (Also incidentally do not use @@identity, use scope_identity instead or you may have data integrity problems down the road as @@identity will not work oproperly when there are triggers on the table which inset into another table with an identity.)

Take the code in the called stored procedure and rewrite it into set based code.

Use an insert statement that uses a join instead for the inserts.

Avoid using dynamic SQL.

Questions about posting. See faq183-874

 

[RobHVB6Sql]

Wow, thanks people, lots of good ideas here.
I have added an OUTPUT parameters and the stored proc seems to be working fine. - Thank you.

Now my focus is back to VB6 -which calls this, and informing the user when an error occurs.

I have a problem executing /opening the ADO record set and command. I've taken code from this thread,

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

but there is a problem.

PS - SQLSister I cant find anything on 'scope_identity' or 'set based code' in BOL.

Thanks again.

'build sql
TheSQL = strDBowner + "spLoopProcWithRB '" + CurrUser.UserID + "', 'spPostalContactUpdate 0, ', "
TheSQL = TheSQL + _
         "', " & GetListValue(ProviderIDList, comCriteria3.ListIndex) + ", " + _
         Chr(34) & Format(Now, StdDateFormat) & Chr(34) + ", " & Chr(34) + _
         GetListValue(ProviderList, comCriteria2.ListIndex) & Chr(34) + ", " & Chr(34) + _
         "Automatically inserted by Enigma on " + Format(Now, StdDateFormat) + "." & Chr(34) + "'"
             
    TheSQL = TheSQL + ", '" + strRecipient + "', '', ''"
    
    'create an ADO connection with output parameters
    Dim adoEnigmaParamConn As ADODB.Connection
    Dim ADORSparam As Recordset
    Dim cmd As ADODB.Command
    
    Set adoEnigmaParamConn = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set ADORSparam = New ADODB.Recordset
    
    adoEnigmaParamConn.Open "DSN=" & strDSNName & ";UID=" & strUserID & ";PWD=" & strPassword & ";"
    
    With cmd
        .CommandText = TheSQL
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@ErrorDesc", adChar, adParamOutput, 500)
        .Parameters.Append .CreateParameter("@CountClients", adInteger, adParamOutput)

        .ActiveConnection = adoEnigmaParamConn
    End With

    With ADORSparam
        .CursorLocation = adUseClient
        .Open cmd 'crashes here
    End With
        
    'inform user if error occured
    
    If cmd.Parameters("@ErrorDesc").Value <> "" Then
       Call MsgBox(cmd.Parameters("@ErrorDesc").Value & vbLf & vbLf & _
       cmd.Parameters("@CountClients").Value & _
       " client records were created out of " & intProgressMax & ".", _
       vbCritical, "Correspondence Error. Please inform Data Manager")
    End If
    
    adoEnigmaParamConn.Close

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

 

[hmckillop]

for the scope_identity change replace the line

      SELECT @ContactID = CONVERT(VARCHAR, @@IDENTITY)
[code]
with
[code]
  SELECT @ContactID = CONVERT(VARCHAR, scope_identity())

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

I aslo forgot to ask, What error do you get from the code?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

Set-based code processes all the records in one step vice one record at at time as a cursor does. Inserts and updates for instance should virtually never be done using a cursor as they are very slow. suppose you want to update 200,000 records. A cursor will run the update statment 200,000 times, a set-based statement to do the same thing will update all the records in one pass. Replacing a cursor can save minutes, even hours, of processing time.

Example of a set-based insert:

INSERT Table1 (Field1, Field2, Field3)
Select field4, Fiedl2, Field5 from table2 where Field6 > Getdate()

Example of a set-based update:

UPDATE Table1
SET Field1 = Table2.Field2*100
FROM Table1 join Table2
ON Table1.IDField = Table2.IDField
WHERE Table2.Field3 = 'sometext' and table1. Field1 is null

Questions about posting. See faq183-874

 

[RobHVB6Sql]

1) scope_identity code gives me an error on compile.
'scope_identity' is not a recognized function name.

2) Thanks for set based code advice /examples.

3) Current error occurs here ###

  With ADORSparam
      .CursorLocation = adUseClient
      .Open cmd 'crashes here ###
  End With

Error is not very helpful:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

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

 

[donutman]

Be sure that you put the parenthesis after scope_identity (). To fix the error replace this code:

    With cmd
        .CommandText = TheSQL
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@ErrorDesc", adChar, adParamOutput, 500)
        .Parameters.Append .CreateParameter("@CountClients", adInteger, adParamOutput)

        .ActiveConnection = adoEnigmaParamConn
    End With

    With ADORSparam
        .CursorLocation = adUseClient
        .Open cmd 'crashes here
    End With

with the code below. You don't need ADORSParam, because you aren't returning a rowset to the app.

    With cmd
        .CommandText = "spLoopProcWithRB"
        .CommandType = adCmdStoredProc
        .ActiveConnection = adoEnigmaParamConn
        .parameters.refresh
        .parameters(1)=<userID>
        .parameters(2)=<ExecuteString1>
        .parameters(2)=<ExecuteString2>
        .parameters(2)=<Recipient>
        .execute
    End With

You definitely need to do as SQLSister suggests regarding the use of cursors. Take the extra time to do it right AND become more familiar with set-based logic...it will pay dividends. Trust me, the VB and SQL code for this application will be MUCH BETTER.
-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]

 

[RobHVB6Sql]

OK fine, I see the advantages of 'Set-based code processes' and have started to convert the sp to this, but will it do what I need?
For each row in tblTempClientList I need to insert a record (set-based would be fine).
But the next step requires each returned ID (from the table's identity column) to be used for a second table insert.

So, I may need to write this back to tblTempClientList.
Or can I write one statement to do this.
I'm already getting errors from SQL re this code:

BEGIN TRANSACTION

   -- prepare & execute some dynamically constructed SQL
   SELECT @ExecSQL = 
   'INSERT INTO tblPostalContact
          (PersonID      , 
           ProviderID    , 
           ContactDate   ,
           Correspondence,
           Notes         ,
           Created)
    SELECT tcl.PersonID, 
           @ProviderID, 
           @ContactDate,
           @Correspondence,
           @Notes        
           User + ', ' + CONVERT(varchar,getdate()))
    FROM   dbo.tblTempClientList tcl
    WHERE  ProcessedWhen IS NULL
      AND  UserID = @UserID'

ERROR = A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

At the moment this is getting a little complex and time consuming, thanks for your help, but I just need this working!

PS scope_identity still not working, but @@identity is so I will leave it.

Rob Hasard
(VB6 /SQL 7.0)

 

[donutman]

EGADS, I forgot the @@ in front of @@Scope_Identity! I'll work on thre rest soon as I can.
-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]

 

[donutman]

OK, besides eliminating the cursor, we are going to eliminate the dynamic SQL which is also a no-no. What does this SP have to do and what information (input parameters) does it need from the application?
-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]

USe this instead of the statement above. You don;t need dynamic SQL for this statment. Note that the same value of the variables will be inserted into all records. If you don;t want that result you need to do something else.
INSERT INTO tblPostalContact
(PersonID ,
ProviderID ,
ContactDate ,
Correspondence,
Notes ,
Created)
SELECT tcl.PersonID,
@ProviderID,
@ContactDate,
@Correspondence,
@Notes
User + ', ' + CONVERT(varchar,getdate()))
FROM dbo.tblTempClientList tcl
WHERE ProcessedWhen IS NULL
AND UserID = @UserID

Questions about posting. See faq183-874

 

[hmckillop]

donutman,
I dont think there is an @@ infront of scope_identity?

Rob
In order to get it working you would use code similar to this

DECLARE @v_NewIdentity bigint
DECLARE @v_table table (myId int identity(101,11), field2 varchar(11))
INSERT INTO @v_table ( field2) values('hello')
INSERT INTO @v_table ( field2) values('hello')
INSERT INTO @v_table ( field2) values('hello')
SELECT @v_NewIdentity = SCOPE_IDENTITY()
SELECT @v_NewIdentity 
--it should return 123 - id of 3rd row i.e. 101 + 11 + 11
[code]

"I'm living so far beyond my income that we may almost be said to be living apart

 

[donutman]

hmc, you're right about @@. It sucks being over 50!
Rob, you mentioned that you need to enter another record in a 2nd table after each insert. There is a way to have the SQL Server do that by using a trigger. However, my choice would be to have the app do it. The SP can return the identity value after the first insert. Another SP can do the 2nd insert. BTW, you must get the scope_identity() working. Don't use @@identity!
-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]

 

[RobHVB6Sql]

hmckillop /SQLSister. Thanks, but once again Scope_Identity() returns "'SCOPE_IDENTITY' is not a recognized function name." Is this available in SQL 7.0? Or maybe its a setting on the server which I have no control over.

Donutman:
* This SP's purpose (spLoopProcWithRB):
Create two records for each row in a table (see my posts above).
The first is easy, the second required the returned @@IDENTITY from first insert to create the second row. Most importantly the whole process can be rolled back at ANY time.
Input parameters sent from the app:
(** denotes value is same for all records)
@UserID VARCHAR (20),
@ProviderID INTEGER, **
@ContactDate DATETIME, ** {today}
@Correspondence VARCHAR (20), **
@Notes VARCHAR (255), **
@Recipient VARCHAR (20), **

LATEST CODE IS:

/* spLoopProcWithRB     Last updated: 02/11/04 (RH)      */
/* 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),
   @ProviderID     INTEGER,
   @ContactDate    DATETIME,
   @Correspondence VARCHAR (20),
   @Notes          VARCHAR (255),

   @Recipient      VARCHAR (20),
   @ErrorDesc      VARCHAR (500) OUTPUT,
   @CountClients   INTEGER       OUTPUT
)
AS

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 @ContactIDscope INT       	     -- ContactID is the identity of the new row inserted here 

SELECT @CountClients = 0 -- nice to return this to the app (VB6) as a check

--for each row in the table ... 
-- WHILE (@@FETCH_STATUS = 0) ??
BEGIN

   -- start a transaction
   BEGIN TRANSACTION

  -- fetch first row
  SELECT PersonID
  FROM   dbo.tblTempClientList
  WHERE  ProcessedWhen IS NULL
    AND  UserID = @UserID

   -- prepare & execute some dynamically constructed SQL
   INSERT INTO tblPostalContact
          (PersonID      , 
           ProviderID    , 
           ContactDate   , 
           Correspondence, 
           Notes         , 
           Created) 
   SELECT tcl.PersonID, 
           @ProviderID,
           @ContactDate,
           @Correspondence,
           @Notes,
           User + ', ' + CONVERT(varchar,getdate())   
   FROM  dbo.tblTempClientList tcl
   WHERE ProcessedWhen IS NULL
     AND UserID = @UserID

   -- if there was an error...
   IF @@ERROR <> 0 
   BEGIN
      SELECT @ErrorDesc = 'Postal contact record error. PersonID:' + CAST(@PersonID AS CHAR(4)) + "."
      --PRINT @ErrorDesc /* TESTING ONLY */
      ROLLBACK TRANSACTION
      RETURN 1
   END
   ELSE
   BEGIN
      /* save the generated ContactID to return */
      SELECT @ContactID = CONVERT(VARCHAR, @@IDENTITY)
--      SELECT @ContactIDscope = SCOPE_IDENTITY() --Changed from @@IDENTITY as per TekTips hint
      -- ERROR HERE 'SCOPE_IDENTITY' is not a recognized function name. NB: Using SQL 7.0.

-- THIS SP has returned the identity, now start another SP to do the 2nd insert. 
-- Wrap this in a rollback transaction also
    INSERT INTO tblPostalContactDetails (ContactID, Type, ReferenceCode)
    VALUES (@ContactID, 'Postal Recipient', @Recipient)

   END
END -- while /get next row

   -- finish the transaction
   COMMIT TRANSACTION

Rob Hasard
(VB6 /SQL 7.0)

 

[SQLSister]

COuld be that the function isn't in SQL Server 7.0, you need to check BOL to be sure.

Questions about posting. See faq183-874

 

[donutman]

It looks like you haven't set the 2nd output parameter. You have this code that you don't need unless you want to set @CountClients.

 -- fetch first row
[Blue]SET[/Blue] @CountCleints[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray]PersonID[Gray])[/Gray]
       [Blue]FROM[/Blue] dbo.tblTempClientList
       [Blue]WHERE[/Blue] ProcessedWhen IS [Gray]NULL[/Gray] [Gray]AND[/Gray] UserID [Gray]=[/Gray] @UserID[Gray])[/Gray]

But since you are working with a single @UserID will there be more than one row? Otherwise everything looks kewl.
-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]

 

[RobHVB6Sql]

Looking good now. SP reported errors back to the application via output parameters. Only two question left!

1) How do I loop through the appropriate rows (UserID = @UserID) in the tblTempClientList table?
* I could use a cursor ;-)
* What about creating a #temp table and grabbing each row from this? I have seen posts here that looping through a table is a bad idea.

2) Rollback. I'm still a little worried about this.
Inserting into two tables here and I need the ability to rollback at the last / any stage. Ie if 300 rows inserted at step one and it fails on row 299 of the second step - rollback EVERYTHING done. Is my logic here correct?

PS SCOPE_IDENTITY() is not available in SQL 7.0.

It will be impressive to see all this done without cursors or dynamic SQL.

Rob Hasard
(VB6 /SQL 7.0)