I have an App that is the front end to a SQL 2000 DB.  The business rules in the App no longer fullfills all the business requirments.   At while ago a consultant was brought in and created a trigger on one of our tables that connects to a seperate database and gets some details that are used to also update the row that is being updated.

The consultant was not that goog.  Whilst the trigger does what it is supposed to, it does not take account of multiple updates.  Whilst these do not occur often when they do it all falls over.  I have amended the trigger to wrap it in a cursor that is Fast_Forward only.

Since doing so I have experienced a:

Database error:[Microsoft][ODBC SQL Server Driver] Connection is busy with results for another hstmt(0)( rc=0)

When I use SQL Query analysiser to execute queries it works fine, but the application does some crazy stuff with preping cursors, etc.

Can anybody help?

A abreviated version of my trigger is below.

Many Thanks

Mike T


DECLARE Assets  cursor FAST_FORWARD READ_ONLY for Select S_ProductCode, S_Barcode, S_SerialNumber,S_Description, S_Supplier FROM inserted

    open Assets
    FETCH NEXT from Assets into @TypeCode, @Barcode, @Serial, @Description, @Supplier
     While @@FETCH_STATUS=0
     BEGIN

        //Do some stuff and contact other database on same server with a select command.

        UPDATE Assets
        SET   Assets. s_Productcode= @TypeCode,
                 Assets.S_AssetType = SUBSTRING(@TypeCode, 1, 8),
                 Assets.S_Description = @Description,
                 Assets.S_Supplier = @Supplier
    WHERE Assets.S_Barcode = @Barcode
   END
  END
FETCH NEXT from Assets into @TypeCode, @Barcode, @Serial, @Description, @Supplier
END
close Assets
deallocate Assets

Geez! I hope he did it for free. :) Next time hire someone that reads this
news group.

Without seeing the entire trigger all I can say is try finding a way to use
UPDATE...FROM inserted <appropriate qualifier> join Assets...

Whatever that cursor does can most probably be done using the set-based
approach.

The uglier option would be to encapsulate this cursor with another one that
goes through the inserted virtual table - in order to support multirow
inserts. But that would be even more lame. In every meaning of the word.


ML

---
http://milambda.blogspot.com/

Thanks for the advice.  Had a look at rewriting it, and have managed to
reduce the trigger in size by about two thirds and achieve everything in a
single statement.

Thanks again for giving me the steer.

Mike

Show quoteHide quote

And how did the change affect performance?


ML

---
http://milambda.blogspot.com/

Problem with DateAdd & GetDate()
Multiple-step OLE DB operation generated errors.
Application - document locking
Ok, time to start a war
Replacement of C code with C# code