6

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.

Ben Wyatt
  • 399
  • 4
  • 14
  • Kinda heavy-handed, but can you transfer data (not using SSIS) from the re-created database to a static database each time it is re-created, and refer to the static database in the view. I'm not sure there's any way to get SSIS to play nice with a database that gets dropped and re-created. Unless you re-create your SSIS package everytime as well. – Tab Alleman May 15 '13 at 15:02
  • What version of SSIS are you using? – Kyle Hale May 15 '13 at 15:14
  • There is some related discussion [here](http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/0223b695-f698-41a6-8ddc-deabd6306aae/). – criticalfix May 15 '13 at 15:26

1 Answers1

11

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.

Kyle Hale
  • 7,912
  • 1
  • 37
  • 58
  • I'm already doing #2. I over-simplified it when I sanitized the error to post here. Trying #1 right now. – Ben Wyatt May 15 '13 at 15:51
  • It looks like #1 did the job. I had to run it on the server that hosts the SP, but it ran successfully after that. Is there a way to limit the scope to one database, though? I really don't want to flush the procedure cache for all my databases every time this runs. – Ben Wyatt May 15 '13 at 16:23
  • 3
    DBCC FLUSHPROCINDB(db_id(@DatabseName)); – Kyle Hale May 17 '13 at 15:53
  • in once instance doing only #2 worked for me, in a different instance it did not so #1 solved the issue for me – MobileMon Sep 27 '14 at 15:39