"Attempt to initiate a new Adaptive Server operation with results pending" error

[washburn_it]

Hi,

here I am back with another error 
So...I'm writing some data (from a Raspberry PI3) into a SQL Server (Express version, using TMSSQLConnection) table located in a Windows machine.
To this table is present a trigger (fired during an INSERT, not written by me) that performs some operations with the inserted data.
Everytime I write a record into this table (using TSQLTransaction.Commit) the error in the subject appears.
If I disable the trigger everything works but if I insert a record from a Windows program (with the trigger enabled) the operation is executed without errors.
Is it a bug of the TMSSQLConnection+TSQLTransaction Raspberry components?
I'm using FreeTDS libraries.
Searching on the internet I found that to avoid this error I should activate "TSQLQuery.PacketRecords=-1" but I'm not using a TSQLQuery to insert records but "TMSSQLConnection.ExecuteDirect" command.
I tried also to save records into a different table where there is a trigger that writes the inserted record into the other table but the error still appears.
What's the reason of this error?
Thank you, regards.

Roberto

[mig-31]

Hi,

there is not answer on your question, but there is no sense to use MS SQL Express server to save data from Rasberry Pi. There are other free/open source databases without limiting FireBird, MySQL/Maria DB, Postgre SQL.

[washburn_it]

I must use it since the SQL database is part of a software to manage realtime production data where the Raspberry is the device that collect those data.
So I need to save those data into the SQL database where the "backoffice" (Windows side) then will read them.


Roberto

[LacaK]

It is strange, that you receive this error when you update data.
This error is usualy related to select, where not all records are fetched and another new operation on same connection is attempted.
Do you use only ExecuteDirect and only with insert/update/delete ?
(btw. it can not be related directly to trigger)
Which version of FreeTDS library do you use ?
May be that trigger generates some kind of output which is not handled by library ... when you perform such update from Microsoft Management Console or other SQL client tool do you see any output ?
Try set SET NOCOUNT ON in trigger (sometimes when SP generates "rows affected" it must be also handled by library)

[washburn_it]

Thanks LacaK,

I will try to add that SET NOCOUNT ON.
Regards,


Roberto