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.
|SELECT FROM REMOTE TABLE DIRECT WITH 4 PART NAME
|SELECT FROM REMOTE VIEW DIRECT WITH 4 PART NAME
|SELECT FROM LOCAL VIEW USING 4 PART NAME TO TABLE
|SELECT FROM LOCAL VIEW USING 4 PART NAME TO VIEW
|SELECT FROM LOCAL VIEW USING SYNONYM TO REMOTE TABLE
|SELECT FROM LOCAL VIEW USING SYNONYM TO REMOTE VIEW
|SELECT FROM LOCAL SYNONYM TO REMOTE VIEW
|SELECT FROM LOCAL SYNONYM TO REMOTE TABLE
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)