View plus Synonym plus Linked Server plus Table equals Trouble

Whilst synonyms can be a very useful tool for abstraction I am a firm believer that they shouldn't change the behaviour of queries that use them, they should simply be alternative names for objects (local or remote). 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.

I ran the following tests (script is attached to post) to check combinations of objects that would encounter this error and the only one was local view->synonym->remote table.

TEST RESULT
SELECT FROM REMOTE TABLE DIRECT WITH 4 PART NAME OK
SELECT FROM REMOTE VIEW DIRECT WITH 4 PART NAME OK
SELECT FROM LOCAL VIEW USING 4 PART NAME TO TABLE OK
SELECT FROM LOCAL VIEW USING 4 PART NAME TO VIEW OK
SELECT FROM LOCAL VIEW USING SYNONYM TO REMOTE TABLE ERROR
SELECT FROM LOCAL VIEW USING SYNONYM TO REMOTE VIEW OK
SELECT FROM LOCAL SYNONYM TO REMOTE VIEW OK
SELECT FROM LOCAL SYNONYM TO REMOTE TABLE OK

The workaround is to either not use a synonym (not ideal) or create a "table view" (simple view doing a select * from the table) on the remote server and referencing that in the synonym.

** Note ** If you are using replication and are publishing views you will encounter this issue when the snapshot agen runs. It calls sp_MSreplupdateschema which as it suggests, updates the schema version of objects. If the object is a view referenced by a remote synonym in a view you will get the schema version error. The way to fix this is to call sp_refreshview on the local view containing the synonym (or don't publish the views)

Update This has been confirmed as a bug and will hopefully be fixed in a future release (see Connect Item 378549)

 

Published 20 October 2008 20:01 by sqldbatips

Comments

# Strategy for consolidating databases | keyongtech

Pingback from  Strategy for consolidating databases | keyongtech

# Alex Merkulov’s Web Notes » The OLE DB provider “SQLNCLI” for linked server “REMOTESERVER” reported a change in schema version between compile time (”170677722198508″) and run

Pingback from  Alex Merkulov’s Web Notes » 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””.

24 August 2012 03:09 by Bug Fix SQL Server 2012 | sql Hammer

# Bug Fix SQL Server 2012 | sql Hammer

Pingback from  Bug Fix SQL Server 2012 | sql Hammer

07 February 2013 22:16 by SQL Server

# Bug: Local View + Synonym + Remote Query

Recently I came across an interesting bug when upgrading a SQL Server 2000 box to 2008 R2. With SQL Server