Pages

Monday, January 30, 2012

Do you know - Accessing Linked server using Synonym results in 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"".
You will hit this issue in the following scenario
1. On SQL Server instance A create a linked server to SQL Server instance B.
2. On SQLS erver instance A, run a Select query like “select * from SQLServer2.Aventureworks.dbo.address”. This should return a valid result set.
3. On SQL instance A create a synonym by running the following command in query window.
Create Synonym syn_name For SQLServer2.Aventureworks.dbo.address”
4. On SQL Instance A create a view on the synonym by running the following command
Create View vw_name
As
Select
* from syn_name
5. On SQL instance A, run 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 SQL Instance B rebuild an index on the table “Address”
Alter index PK_Address_AddressID on address rebuild
7. Now on SQL instance A select from the view “Select * from syn_name”.
This will fail with Error: 7359

To workaround this error, run the sp_refreshview command before executing the view
exec sp_refreshview vw_name
Select * from vw_name

No comments:

Post a Comment