Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Stored Procedure returns schema version change error when run from SSIS, but not when run directly

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

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)".

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

The view refers to the linked server OtherServer. The database referenced on the linked server is dropped and re-created on a nightly basis.

So far, I've tried these things:

1) Dropping and re-creating the view before running the MERGE.

2) Defining the SP that contains the MERGE WITH RECOMPILE.

3) Wrapping the MERGE statement in EXEC() so it wouldn't be compiled in advance.

4) Setting Bypass Prepare to true on the relevant step in SSIS.

Edit:

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

like image 288
Ben Wyatt Avatar asked May 15 '13 14:05

Ben Wyatt


1 Answers

So the problem is you're using a synonym for the linked server's objects, which doesn't play nicely with OLEDB's metadata catalog (that's what generates those numbers you see in the error message.) There are two solutions to this:

1) Call

DBCC FREEPROCCACHE

on the linked server. Since the database is dropped every day anyway, clearing the cache might not be such a burden on other users of the database.

2) Use full four part notation (ServerName.DatabaseName.SchemaName.ObjectName) in your stored procedure.

like image 155
Kyle Hale Avatar answered Oct 24 '22 11:10

Kyle Hale