The OLE DB provider "SQLNCLI" for linked server "X" reported a change in schema version between compile time

  • SQLdevotee

    SSCarpal Tunnel

    Points: 4820

    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]

  • a_digital_nomad

    SSC Rookie

    Points: 39

    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.

  • SQLdevotee

    SSCarpal Tunnel

    Points: 4820

    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]

  • Glen Sidelnikov

    SSCertifiable

    Points: 6727

    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.

  • sethwilpan

    SSC Enthusiast

    Points: 163

    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

  • ryan.davidson

    SSC Veteran

    Points: 288

    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.

  • ryan.davidson

    SSC Veteran

    Points: 288

    Apparently I was wrong, that cummulative update does not fix the issue as it said it would. Back to square 1 with MS again.

  • Martin Stephenson

    SSCrazy

    Points: 2838

    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

  • ryan.davidson

    SSC Veteran

    Points: 288

    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.

  • tmurphy 95903

    SSC Journeyman

    Points: 89

    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

  • BeverleyM

    Say Hey Kid

    Points: 682

    Glen Sidelnikov (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.

  • Dennis Post

    SSCarpal Tunnel

    Points: 4473

    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



    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

  • Anuj Soni

    SSC Enthusiast

    Points: 106

    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

  • jkoon-760461

    Ten Centuries

    Points: 1049

    Can anyone explan why an index rebuild causes this error? An index rebuild is not a "schema change".

  • Reo

    Hall of Fame

    Points: 3774

    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 16 total)

You must be logged in to reply to this topic. Login to reply