Troubleshooting : SQL Compilation Error

A problem encountered by a developer here.

The OLE DB provider "SQLNCLI" for linked server "LINKED_SRVNAME" reported a change in schema version between compile time ("40575232373724308") and run time ("40575232373723940") for table ""DB"."dbo"."Tbl_Test"".

1) locate the code calling this script (i already know it is a view) -

select table_name 
  from information_schema.views 
 where view_definition like '%Tbl_Test%' 

view name returned - vw_tblTest

2) attempt to return data from the view -

select * from vw_tblTest

error returned -

The OLE DB provider "SQLNCLI" for linked server "LINKED_SRVNAME" reported a change in schema version between compile time ("40575232373724308") and run time ("40575232373723940") for table ""DB"."dbo"."Tbl_Test"".

3) check what the view is doing -

create view [dbo].[vw_tblTest] as
select 
    column1,
    column2,
    column3
from
    dbo.SYN_tblTest

so the view is calling a SYNONYM !

4) check what the synonym is doing -

create synonym [dbo].[SYN_tblTest] for [LINKED_SRVNAME].[DB].[dbo].[Tbl_Test]

5) attempt to run the query from the view -

select 
    column1,
    column2,
    column3
from
    dbo.SYN_tblTest

Success! data is returned!

So the query itself works, but the view does not. The plan being used for the view is no longer valid.

Basicly, Tbl_Test on the remote server had changed.
Even though all the columns being requested in the view were still present (the table had only had 2 columns added), the change to the table occured since the view was compiled.

The solution? Recompile the view.

Either recreate the view (alter view [dbo].[vw_tblTest] .... ) (This is what I did, and it worked)

or

DBCC FREESESSIONCACHE (This clears the cache used by distributed queries i.e. the linked server)

reference : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2137045&SiteID=1