Recently I came across an interesting bug when upgrading a SQL Server 2000 box to 2008 R2. With SQL Server 2005, 2008, and 2008 R2, there is a situation where you are calling a local view that is also using a synonym and accessing a remote table and then the remote table’s index is rebuilt. In this situation the synonym should not fundamentally change the way the query operates but in the versions listed you will receive the below 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 occurs because the execution plan of the view is set at compile time and the act of creating and dropping an index is in fact a schema change. As described here, with an index rebuild you are affecting a schema change and therefore the schema versions will be different and throw the error above.
This bug has been accepted by Microsoft (as referenced here) and has been fixed in SQL Server 2012 RTM. See below for a test case that reproduces this bug.
I have used the same test case with 2012 and witnessed that the bug is in fact fixed.
Work Arounds:
- Re-write the process to exclude any one of the necessary factors which cause the bug.
- Create a local view on the remote server and referencing that in the synonym.
- Use sp_refreshview before calling the view.
- This was the simplest fix in my situation because no one wanted to affect a code change, this ran in a SQL Agent job and the view was only used once a month. So I just added a new step prior to the stored procedure call to refresh the view.
Test Case:
The following script runs in SQLCMD mode and will generate the necessary test case and provides print out statements to walk you through to successful and buggy situations.
The test script was pulled from a post here by Jasper Smith.
Download: linked server view synonym tests.txt
Leave a Reply