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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With