Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The OLE DB provider "SQLNCLI" for linked server "X" reported a change in schema version between compile time Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2008 9:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:39 AM
Points: 214, Visits: 568
Hi ,

I encountered the following error mssg

The OLE DB provider "SQLNCLI" for linked server "X" reported a change in schema version between compile time ("A") and run time ("B") for table ""database"."schema"."tablename"".

Anyone knows what steps I can take to resolve this problem?


Imagination is more important than knowledge-Albert Einstein
Post #542765
Posted Thursday, September 25, 2008 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 20, 2011 9:01 AM
Points: 1, Visits: 7
In my case the "tablename" the error was referring to was a table that i had a pass-through view to from the local db. What I mean is, my app had a view called (e.g.) "User". That view was a "select * from User_Table_In_Another_Db".

When I changed deployment environments (moving the local db into an environment where the view was now reading from a different instance of "other_Db"), the view had apparently retained some kind of version information from the first "other_Db" it was looking at.

Recompiling the views solved the problem. However, the reason I'm out here looking for people who are experiencing the issue is... for whatever reason, the error has resurfaced. I don't have direct access to the DBs, as I am in a consulting role, remote, and they won't allow it. But they say they haven't changed anything about the schema in "other_Db" since we fixed the issue by recompiling the views.

So now I'm puzzled again. But I thought, if your "tableName" was a view you could try this.
Post #576148
Posted Thursday, September 25, 2008 1:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:39 AM
Points: 214, Visits: 568
Exactly!, I have a view as table name. But I have spoke with Microsoft regarding this and they accept that this is a bug in SQL which they are not about to fix anytime soon. So, I had to use sp_recompile and/or sp_refresh to be able to get those two table linked and be able to use them. I didn't like the temporary fix but I have to go on with it for my clients to be able to access the database.

Imagination is more important than knowledge-Albert Einstein
Post #576341
Posted Thursday, September 25, 2008 1:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
There is one more scenario. It is using synonyms in SQL 2005.
In this case this is "granted by design". You need to recreate synonym if the table on which synonym was based changed.
Post #576349
Posted Saturday, December 18, 2010 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 4, 2014 4:02 PM
Points: 3, Visits: 84
You can also use dbcc freeproccache, which will prevent the error from occurring until the next time the schema on the linked server changes.


Seth Wilpan
Post #1036887
Posted Thursday, April 28, 2011 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 10:32 AM
Points: 14, Visits: 66
Apparently MS has a fix for it now.
http://support.microsoft.com/kb/2498818

There is a cumulative patch that can be requested through a support call.
Post #1100491
Posted Friday, June 3, 2011 3:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 10:32 AM
Points: 14, Visits: 66
Apparently I was wrong, that cummulative update does not fix the issue as it said it would. Back to square 1 with MS again.
Post #1119733
Posted Monday, June 20, 2011 4:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 13, 2014 5:54 AM
Points: 453, Visits: 583
I get this error in SQL2008 R2, but I get it on a scheduled job, when i recreate an underlying view it goes away for a few days and then comes back, other than trapping the error and forcing a rebuild of the view I cant seem to find a way of stopping the job failure
Post #1128059
Posted Monday, June 20, 2011 8:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 10:32 AM
Points: 14, Visits: 66
On my side, the error occurs every Monday morning after the rebuild of indexes and stats. So, maybe removing the autostats update and put that on a schedule and after that job is done, then do a drop and re-create of synonyms would be a better workaround.

We've been talking with an engineer from MS on this issue, and they've seen the error and confirmed that the patch that supposedly fixes it is installed too. But, there is a 3rd party SQL box that is a target of the synonyms that wasn't patched, so we're in the process of getting okays from JDE that they will support that patch and if so, then we'll see if that solves the problem.

From what I've seen, all the servers (target and consumer of synonyms) need to have this patch installed, not just the server where the error is occurring.

Hope that helps.
Post #1128280
Posted Thursday, October 20, 2011 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 5:22 PM
Points: 1, Visits: 2
Recently I came across an issue with views that use synonyms to reference tables in a remote database across a linked server. When the indexes are rebuilt on the remote table, queries that reference the view containing the synonym fail with the following error

The OLE DB provider "SQLNCLI" for linked server "REMOTESERVER" reported a change in schema version between compile time ("170677722198508") and run time ("170677722198546") for table ""tempdb"."dbo"."remotetable"".

This combination of local view->synonym->remote table is the only combination of objects that suffers from this issue. [highlight=#ffff11]If you modify the view to reference the remote table using a four part name then the issue does not occur.[/highlight] This is my issue with this error, the synonym is altering the behavior of the query in a non-intuitive way. If the synonym was truly just another name for the remote table it would not encounter this error so to my mind this is a bug. Also occurs on SQL 2008.

===========================================================================================

Posted by Microsoft on 6/8/2010 at 8:54 PM

We fixed the bug related to the schema version error when using synonyms in a local view to reference a remote table. The fix will be in the first CTP (CTP0) of the coming release (SQL Server 11 Codename Denali). We are currently also considering backporting it to earlier versions but the decision has not been made yet.

Thanks for your patience.

Regards,

Joachim Hammer
Program Manager
SQL Server
Post #1193768
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse