Home Dashboard Directory Help
Search

Synonym in local view to remote table causes schema version error by sqldbatips


Status: 

Closed
 as Fixed Help for as Fixed


23
0
Sign in
to vote
Type: Bug
ID: 378549
Opened: 10/30/2008 10:39:50 AM
Access Restriction: Public
1
Workaround(s)
view
20
User(s) can reproduce this bug

Description

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.

Details
Sign in to post a comment.
Posted by Dan Price on 10/2/2012 at 7:12 PM
I too have experienced this issue on version 10.0.5500.0
Posted by Doctorj101 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?
Posted by Satya SKJ on 10/11/2011 at 1:58 AM
KB2498818 has no affect to fix the issue, it remains!
Posted by AlreadyPicked 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).
Posted by kressir on 1/10/2011 at 12:45 PM
Joachim, any update on this getting backreported to earlier versions of SQL?

Thanks,
Posted by AlreadyPicked 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?

Thanks.
Posted by Microsoft 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.

Regards,

Joachim Hammer
Program Manager
SQL Server

Posted by uyadava 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.
Posted by pliant on 3/15/2010 at 12:37 PM
Hi Joachim,

Can you tell me exactly what release this was fixed in?

Thanks!
Posted by EWhitlow 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.
Posted by Andeezle 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.
Posted by sqldbatips 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!
Posted by Klaus Ondrich 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?

Best regards,

Klaus Ondrich
RHI AG
Posted by EWhitlow 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.
Posted by Microsoft on 11/6/2008 at 11:02 PM
Dear Jasper,

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.

Joachim Hammer

Program Manager
SQL Server
Sign in to post a workaround.
Posted by John R_ 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
File Name Submitted By Submitted On File Size  
linked server view synonym tests.txt (restricted) 10/30/2008 -