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 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
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.
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”
“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
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
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
Select * from vw_name
No comments:
Post a Comment