Connection is busy with results for another hstmt

[johnatpebble]

Hi,

I'm trying to implement a trigger on a SQL 2005 DB that runs as a mirror of an external application over which I have no control. I need to run the trigger because this DB doesn't store insert and update timestamps which I need to efficiently synchronise with the data (trigger on insert and update puts timestamp into field).

This process causes the [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt.

As the app is external I don't think I can use MARS as I can't influence the connection.

Is there any other way to implement this methodology?

Thanks
John

 

[Crowley16]

sorry, I'm not clear on what you're actually asking here...

so, you have an external app, which you can't touch.

This app, inserts/updates data in sql server.

you want the server to log when this app inserts or updates data.

A trigger seems to be perfect for this, and it seems like you have set one up.

Is the trigger not working?
what does MARS have to do with anything?
The error you're quoting doesn't seem like anything to do with a trigger issue, what's it for?

--------------------
Procrastinate Now!

 

[johnatpebble]

Hi, Thanks for your reply.

Yes, the trigger is created and works. The problem I think is that 2 result sets are returned when the application is only set the deal with one (maybe - I'm not so good with the ins and outs of SQL server).

Whatever it is though, the trigger causes the hstmt error (no error if I take the trigger off). The application uses a system DSN to link to the SQL DB and that is set-up with the native client driver.

Any ideas?

Cheers
John

 

[Crowley16]

so you're using a trigger to return data to your app? doesn't sound like it's a good idea to me.

you could post the code for the trigger and we can check if there's anything obviously wrong with it...

--------------------
Procrastinate Now!

 

[johnatpebble]

Hi,

Thanks for getting back... no I'm not trying to return data with the trigger. The transaction that the external app initiates waits for the return (I assume), and with a trigger on that process more data than it expects/ different data is being returned (I assume). Does that sound likely?

Here is the trigger:

CREATE TRIGGER order_updatedDate
ON [order] AFTER INSERT, UPDATE AS DECLARE @recid int SELECT @recid = (SELECT recid FROM Inserted) INSERT INTO [pbRecordUpdate] ([updatedDate], [tableName], [recid]) VALUES (GETDATE(), 'order', @recid);

(So I'm inserting a record into a table we are using as an update log)

Again, I'm pretty rubbish with this stuff so spare no criticism!

Thanks
John

 

[johnatpebble]

Hi,

Thanks for getting back... no I'm not trying to return data with the trigger. The transaction that the external app initiates waits for the return (I assume), and with a trigger on that process more data than it expects/ different data is being returned (I assume). Does that sound likely?

Here is the trigger:

CREATE TRIGGER order_updatedDate
ON [order] AFTER INSERT, UPDATE AS DECLARE @recid int SELECT @recid = (SELECT recid FROM Inserted) INSERT INTO [pbRecordUpdate] ([updatedDate], [tableName], [recid]) VALUES (GETDATE(), 'order', @recid);

(So I'm inserting a record into a table we are using as an update log)

Again, I'm pretty rubbish with this stuff so spare no criticism!

Thanks
John