SQL Server 2008 R2: OLE DB provider “SQLNCLI10” for linked server “x” reported a change in schema version between compile time and run time

Recently I had an intersting issue at one of the client’s place that pertains to Linked Server issue on a SQL Server 2008 R2 X64 environment.

The OLEDB provider for the linked server reported a change in schema version between compile time and execution time.

For a moment the error throws out spanner in works, application stalls and complaints about timeout issues. A search on forums reveals its a real problem when you perform changes to table schema with REORG or column change in an object. A REORG is quite common on a production environment but not that schema changes, which is managed as a seperate implementation. See this SQLConnect case for further information.

KBA2498818 confirms the scenario why and how it happens, consider the following scenario:

• You create a linked server on a computer that is running Microsoft SQL Server 2005, Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2. The linked server points to an OLE DB data source on a remote server.
• You create a synonym for a table on the linked server.
• You create a user-defined function that uses the synonym, or you create a view that uses the synonym. Then, you run a query against the user-defined function or the view.Note When you run the query, an execution plan is cached.
• You perform an operation on a remote server to update the schema version of the table on the linked server. For example, you rebuild the index of the table on the remote server to update the schema version of the table on the linked server.
• You run another query against the user-defined function or the view.

Ok I understand the default behaviour of the issue and why it occurs, however in the recen times I have been involved in an issue at client’s place which do not involve any database maintenance activities or schema changes.  The application calls up few views that refers to be a remote table on another SQL Server instance, its a X64 environment with build number 10.50.2772 (SQL Server 2008 R2 SP1 CU2).

As per the KBA the issue is fixed in  Cumulative Update package 1 for SQL Server 2008 R2 SP1 – KBA2544793, but not the issue still remains at this moment where none of the symptoms from above KBA are applicable here.

KBA2498818 has conflict of information, as the fix that is described in this article is available for the following versions of SQL Server:

• Microsoft SQL Server 2005 Service Pack 3 (SP3)
• Microsoft SQL Server 2005 Service Pack 4 (SP4)
• Microsoft SQL Server 2008 Service Pack 1 (SP1)
• Microsoft SQL Server 2008 Service Pack 2 (SP2)

loadTOCNode(3, ‘resolution’); Also “The fix for this issue was first released in Cumulative Update 1 for SQL Server 2008 R2 Service Pack 1. For more information about how to obtain this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base: KBA2544793.

So I have raised a support case with Microsoft CSS and taken nearly 4 man-days to discuss, reproduce and confirm with Support engineer. At the end happy that MS agreed the issue still remains in SQL Server 2008 R2 RTM or SP1, and its not fixed with Cumulative Update 1 package for SQL2008 R2 SP1.

Now the eyes are opened up for the SQL Server 2008 R2 SP1Cumulative Update Package 4 release.

Regards

Satya Shyam K Jayanty

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook