Did you know… Accessing Linked server using Synonym can fail with an error 7359

Accessing Linked server using Synonym results in an error after we rebuild Indexes on the target server in SQL Server 2005.

The Error Message is like below,
Msg 7359, Level 16, State 1, Line 1

The OLE DB provider “SQLNCLI” for linked server “Domain\User” reported a change in schema version between compile time (“165944653572647”) and run time (“170368469612430”) for table “”Adventureworks”.”dbo”.”address””.

We get the issue when we go through the below scenario,

1.          On SQLServer1 instance create a linked server to SQLServer2.
2.          On SQLServer1 instance, Test a Select query like “select * from SQLServer2.Aventureworks.dbo.address”. This should return a valid result set.

3.          On SQLServer1 create a synonym by running the following command in query window.
“Create Synonym syn_name For SQLServer2.Aventureworks.dbo.address”

4.          On SQLServer1 create a view on the synonym by running the following command
Create View vw_name
Select * from syn_name

5.          On SQLServer1, Test a select query on the Synonym like “ Select * from syn_name” This should return the same results that you got in Step2

6.          Now on SQLServer2 rebuild an index on the table “Address”

alter index PK_Address_AddressID on address rebuild

7.          Now on SQLServer1 select from the view “Select * from syn_name”.
This will fail with the 7359 Error

The work around for this is to run the sp_refreshview command before executing the view

like below,
sp_refreshview vw_name
Select * from vw_name

Levi Justus
TL, Microsoft SQL Server