The stored procedure returns an error of changing the version of the scheme when starting from SSIS, but not when starting directly

Question

I have a SQL Server stored procedure that runs correctly every time I start manually using EXEC , but when it runs as part of the SSIS package, it fails with this error:

 Executing the query "EXECUTE (ProcName) " failed with the following error: "The OLE DB provider "SQLNCLI10" for linked server "(OtherServer)" reported a change in schema version between compile time ("177833127975044") and run time ("177841717910098") for table (Server.Database.Schema.Table)". 

This procedure is a MERGE statement that combines data from a view into a table in another database on the same server as the SP.

The view refers to the linked OtherServer server. The database, a link to a linked server, is discarded and recreated at night.

So far I have tried these things:

1) Drop and re-create the view before starting MERGE.

2) Defining an SP containing MERGE WITH RECOMPILE .

3) Wrapping the MERGE operator in EXEC() , so it will not be compiled in advance.

4) Setting Bypass Prepare to true at the appropriate step in SSIS.

Edit

SQL Server 2008 is running on the server with the stored procedure. The linked server is 2008 R2.

Answer 1

So the problem is that you are using a synonym for server related objects that doesn’t work well with the OLEDB metadata catalog (which generates the numbers you see in the error message). There are two solutions for this:

1) Call

 DBCC FREEPROCCACHE 

on the linked server. Because the database is discarded every day, flushing the cache may not be such a burden for other database users.

2) In your stored procedure, use the full four-digit notation (server_name .DatabaseName.SchemaName.ObjectName).