Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Connection is busy with results for another hstmt

Status
Not open for further replies.

johnatpebble

Programmer
Apr 29, 2010
5
GB
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
 
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!
 
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
 
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!
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top