July 29, 2008 at 9:23 am
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?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
September 25, 2008 at 9:56 am
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.
September 25, 2008 at 1:07 pm
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.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
September 25, 2008 at 1:14 pm
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.
December 18, 2010 at 9:00 am
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
April 28, 2011 at 12:43 pm
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.
June 3, 2011 at 3:03 pm
Apparently I was wrong, that cummulative update does not fix the issue as it said it would. Back to square 1 with MS again.
June 20, 2011 at 4:31 am
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
June 20, 2011 at 8:23 am
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.
October 20, 2011 at 8:39 am
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
February 25, 2013 at 11:04 am
Glen (9/25/2008)
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.
Ah hah, this was my issue. Thank you, Glen!
I had a synonym on a table; used Red Gate's data compare to synchronize data between test & production tables which dropped indexes and recreated them... which broke the synonym. Recreating the synonym fixed this error for me.
October 16, 2013 at 1:04 am
I also encounter this now and then, but with not any specific regularity.
EXEC sp_RefreshView '<View that reverences a linkedserver table>' will also allow the view to be used.
Has anyone encountered this with a view that uses SchemaBinding?
None of my views that generate this message use schemabinding.
Cheers
October 22, 2014 at 9:32 pm
Hi,
If you alter the view or procedure from which you are calling remote view then it will also resolve your issue of this error.
Alter the view and you will have solution.
Also you can use "DBCC FREEPROCCACHE" at your local server to remove the cache.
Thanks,
Anuj Soni
October 23, 2014 at 10:24 am
Can anyone explan why an index rebuild causes this error? An index rebuild is not a "schema change".
August 30, 2016 at 7:21 pm
Issue : Error "reported a change in schema version between compile time" when queering LinkServer (Remote table).
Solution : Please set LinkServer property "Lazy Schema Validation" to FALSE
If this option is set to false, the default value, SQL Server checks for schema changes that have occurred since compilation in remote tables. This check occurs before query execution. If there is a change in the schema, SQL Server recompiles the query with the new schema.
If this option is set to true, schema checking of remote tables is delayed until execution. This can cause a distributed query to fail with an error if the schema of a remote table has changed between query compilation and execution.
http://msdn.microsoft.com/en-us/library/ms190918.aspx
Ram
MSSQL DBA
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply