Share via

Linked Server schema caching results in query errors

  • Article

Question

Thursday, September 3, 2009 8:48 PM

According to my research Linked Server to any (!) DB has problem querying data after a column is dropped:
Msg 7353, Level 16, State 1, Line 1
The OLE DB provider "name" for linked server "TEST" supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

To reproduce, configure Linked Server to Oracle, DB2, MySQL or Postgres (PGNP) and query data using following statement:
SELECT * FROM TEST.DB.SCHEMA.TBL

Then DROP a column in Linked Server (using corresponding database utilities):
ALTER TABLE TBL DROP COLUMN X

The error (see above) will be returned. After this nothing helps to return Linked Server into normal working state. I've tried rebooting SQL Server, recreating Linked Server, etc.

However, it appears that the issue is not happening when two MS SQL Servers are connected (e.g. from SQL Server 2005 to SQL Server 2000). Which makes MS OLEDB Provider somewhat special.

Question: what can vendor of an OLEDB provider do to fix this problem (as it is done in MS OLEDB provider)?

All replies (9)

Monday, September 7, 2009 7:52 AM âś…Answered | 1 vote

Hi

According to the current information, we could know it is a special driver problem; to resolve the issue, we need to involver the driver vendor:
for example: for Oracle, we need to involve the Oracle engineer; for DB2, we need to involve IBM engineer.

Regards

Friday, September 4, 2009 6:46 AM

Does the "lazy schema validation" option of sp_serveroption make any difference?Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Friday, September 4, 2009 2:46 PM

Yes, I have tried both values, true and false, for the option but it did not make any difference.

This issue is very easy to reproduce and we can see that MS OLEDB knows how to work right. How can other vendors fix the issue in their providers?

Tuesday, September 8, 2009 5:41 PM

Hello Mark,
I'm a developer of OLE DB provider for PostgreSQL (www.pgoledb.com) and I represent the vendor. How do we communicate about this issue?
Should I provide my business e-mail/phone here?

Thank you - this is what I hoped for!

(I'm actually phoenicyan - just logged in using a different account)

Wednesday, September 9, 2009 2:41 AM

Hi Izm_ka

in order to better assist you, I suggest you to submit a case to CS&S service and they will cominucate with you by email/phone to make the problem much clear. Thanks.

Wednesday, September 9, 2009 4:54 AM

Mark,
what is CS&S and how to submit a case? Could you provide a url to the service?

Thank you!

Wednesday, September 9, 2009 6:30 AM

hi

you may call our customer service center at (800) 642-7676 Monday-Friday 6:00 A.M.-6:00 P.M. PST.

For more information about the service, there is a link for you:http://support.microsoft.com/gp/professionalboundary/zh-sg

Regards

Monday, November 30, 2009 8:08 AM

Here is update on the topic:

  1. Microsoft had confirmed that there is a defect in SQL Server matadata cache implementation. They have started working on the fix that will probably be available soon for all versions of SQL Server.
  2. Microsoft discovered a bug in PostgreSQL OLE DB Provider. The bug was fixed and is publicly available.

It is still unclear to me how SQL Native Client works around the issues in SQL Server.

Thank you!

Wednesday, January 27, 2010 5:13 AM

Update:
A bug was fixed in PostgreSQL OLEDB provider that almost resolved the issue. Even though the defect in SQL Server's metadata cache still exists. For example, if last column is dropped in a table via linked server, you can no longer query the table. SQLServer has to be restarted to make the query work again (previously, even restart did not help - the table must be dropped).

Do you think that a hotfix for SQL Server 2005 and 2008 is a need? Or you'll prefer to wait and start using 2010 instead?