//
you're reading...
SQL Server

OLE DB provider ‘SQLOLEDB’ could not INSERT INTO table ‘[SQLOLEDB]’. Unknown provider error. OR Turn Off the “no count” default connection property for OPENQUERY inserts

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

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: