SQL Server Home
Synonym in local view to remote table causes schema version error
10/30/2008 10:39:50 AM
User(s) can reproduce this bug
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. If you modify the view to reference the remote table using a four part name then the issue does not occur. This is my issue with this error, the synonym is altering the behaviour 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.
SQL Server 2005 SP2 - Standard Edition
Windows Server 2003 (all x64 editions)
Operating System Language
Steps to Reproduce
See attached script (also available here http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/10/20/view-plus-synonym-plus-linked-server-plus-table-equals-trouble.aspx)
Schema version error for combination of local view->synonym->remote table across linked server
to post a comment.
Please enter a comment.
on 10/2/2012 at 7:12 PM
I too have experienced this issue on version 10.0.5500.0
on 9/6/2012 at 6:52 AM
This issue still exists in version 10.0.5500.0
Does anyone know what version this is resolved in or what patch will fix it?
on 10/11/2011 at 1:58 AM
KB2498818 has no affect to fix the issue, it remains!
on 4/7/2011 at 1:13 PM
I believe the KB is 2498818 : http://support.microsoft.com/kb/2498818
I have yet to test it but it looks like that bug is corrected at least in builds 10.0.4279.00 (SQL Server 2008 SP2 CU3) AND 10.0.2816.00 (SQL Server 2008 SP1 CU13).
on 1/10/2011 at 12:45 PM
Joachim, any update on this getting backreported to earlier versions of SQL?
on 11/30/2010 at 11:06 AM
Joachim, you said : "We are currently also considering backporting it to earlier versions but the decision has not been made yet."
As there been any discussion on this yet? Will you update this "thread" when a decision is made?
on 6/8/2010 at 8:54 PM
Jasper and others,
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.
on 4/12/2010 at 5:19 PM
I got same issue and fixed by refreshing view on local server. Is there any "Knowledge Base" reference for this issue from Microsoft? Or anything mentioned in MSDN/KB some where? Please provide links if exists.
on 3/15/2010 at 12:37 PM
Can you tell me exactly what release this was fixed in?
on 1/30/2009 at 10:46 AM
I have some additional information that I think everybody will find interesting. I have a SQL 2008 server with linked server references to a SQL 2000 server. There are about 100 synonyms referencing the 2000 server. Following a DBCC DBREINDEX of every user table in the databases used by the synonyms I get this error. To fix it I regen (drop/create) a single synonym and it seems to fix everything. I did some additional testing and in SQL 2000 the schema version is updated in the system tables when a DBCC DBREINDEX is done to a table, but SQL 2008 does not.
I could see how that would cause all the synonyms to fail but what I don't understand is fixing one fixes them all.
on 1/27/2009 at 7:57 AM
This bug is a pain in the butt. Referring to remote tables thru a synonym seems like a pretty logical solution. I'm surprised more people haven't encountered this problem. Microsoft, please fix this bug ASAP. At this point, I'm ready to bypass the synonyms which really sucks because referencing a synonym is way better than referencing linked objects directly. Please let us know when you plan to fix this. Thanks, Andy Moyer.
on 11/11/2008 at 6:49 AM
We've hit another issue with this. The workaround for this bug is to modify the synonym used in the local view to refer to a remote view (rather than a table) and this works great at avoiding the error for index rebuilds however if the remote database is published for replication and the view itself is replicated, the replication snapshot agent causes the schema version error because it calls sp_MSreplupdateschema on the view itself!
on 11/10/2008 at 11:20 PM
I confirm this problem. We also encounter it and it is really annoying since it causes many loads to fail.
Joachim, what do you mean by "next release"? Next cumulative update package or next service pack or next SQL Server version?
on 11/10/2008 at 1:31 PM
I have a similar issue related to SQL 2008 - Std with Stored Procedure -> synonym -> remote table. I worked around it with the synonym pointing to a remote view with all the columns of the underlying table explicitly called out. That seems to have worked.
on 11/6/2008 at 11:02 PM
Thanks for reporting the schema version error when using synonyms in combination with local views and remote table access in linked servers. The behavior is indeed incorrect and we will consider fixing this bug in the next release.
Best reagrds and thanks again for your feedback.
to post a workaround.
Please enter a workaround.
on 7/8/2009 at 1:31 PM
use sp_refreshview not the best solution but it works and is easily scriptable if you need something for a maintenance package
linked server view synonym tests.txt (restricted)
© 2014 Microsoft