InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 14:06:20 GMT20http://www.sqlservercentral.com/Forums/Topic542765-145-1.aspx[quote][b]Glen Sidelnikov (9/25/2008)[/b][hr]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.[/quote]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.Mon, 25 Feb 2013 11:04:55 GMTBeverleyMhttp://www.sqlservercentral.com/Forums/Topic542765-145-1.aspxRecently 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 HammerProgram ManagerSQL ServerThu, 20 Oct 2011 08:39:34 GMTtmurphy 95903http://www.sqlservercentral.com/Forums/Topic542765-145-1.aspxOn 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.Mon, 20 Jun 2011 08:23:41 GMTryan.davidsonhttp://www.sqlservercentral.com/Forums/Topic542765-145-1.aspxI 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 failureMon, 20 Jun 2011 04:31:54 GMTMartin Stephensonhttp://www.sqlservercentral.com/Forums/Topic542765-145-1.aspxApparently I was wrong, that cummulative update [b]does not[/b] fix the issue as it said it would. Back to square 1 with MS again.Fri, 03 Jun 2011 15:03:57 GMTryan.davidsonhttp://www.sqlservercentral.com/Forums/Topic542765-145-1.aspxApparently MS has a fix for it now.[url]http://support.microsoft.com/kb/2498818[/url]There is a cumulative patch that can be requested through a support call.Thu, 28 Apr 2011 12:43:12 GMTryan.davidsonhttp://www.sqlservercentral.com/Forums/Topic542765-145-1.aspxYou can also use dbcc freeproccache, which will prevent the error from occurring until the next time the schema on the linked server changes.Sat, 18 Dec 2010 09:00:44 GMTsethwilpanhttp://www.sqlservercentral.com/Forums/Topic542765-145-1.aspxThere 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.Thu, 25 Sep 2008 13:14:50 GMTGlen Sidelnikovhttp://www.sqlservercentral.com/Forums/Topic542765-145-1.aspxExactly!, 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.Thu, 25 Sep 2008 13:07:49 GMTSQLdevoteehttp://www.sqlservercentral.com/Forums/Topic542765-145-1.aspxIn 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.Thu, 25 Sep 2008 09:56:23 GMTa_digital_nomadhttp://www.sqlservercentral.com/Forums/Topic542765-145-1.aspxHi ,I encountered the following error mssgThe 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?Tue, 29 Jul 2008 09:23:47 GMTSQLdevotee