I recently had a user report an issue with a set of stored procedures that inserted rows into a table on a remote server via openquery and a linked server. The procs had run fine for years and had not been modified recently. But on this occasion when the procs ran the user received the following message:
Server: Msg 7343, Level 16, State 2, Line 1
OLE DB provider ‘SQLOLEDB’ could not INSERT INTO table ‘[SQLOLEDB]’. Unknown provider error.
[OLE/DB provider returned message: Cursor operation conflict]
OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ IRowsetChange::InsertRow returned 0x80040e23: Unknown provider error.].
This was a new one to me, nothing I had seen in recent memory. My immediate focus was on the source server and the linked server. I thought maybe the linked server provider definition had been corrupted etc. Everything seemed to be ok with the linked server definition. I tried a manual select via openquery and the linked server and it returned rows no problem. I then tried to duplicate the insert manually and sure enough it came back with the same error. I was stumped. I decided to take a look at the destination server. It was a clustered sql server 2005 instance. I did notice that the cluster had failed over that night. Out of genious or desperation, I am sure the later, I decided to compare the server settings to the original configuration. I had saved them off as a policy. More specifically I was interested in anything that may have changed in the client connection settings. Viola the “no count” was checked on the default connection options where it had not been before. I unchecked it, and re-tried the insert statement. It worked flawlessly.
So, the morale of the story is that the “no count” default connection property must be TURNED OFF for OPENQUERY inserts