Crossing databases with sprocs using TVPs

• jerry-621596

  SSCertifiable

  Points: 6096

  More actions

  July 19, 2010 at 9:52 am

  #221365

  I have a database designed to hold data pulled in from SSIS processing. That DB is then used to verify, delete, and insert the received data into up to 13 other databases.

  A sproc in this database calls "worker" sprocs in each of the other databases to perform processing.

  Here is my issue: I am trying to collect parameters with the "master" sproc and pass them to the "worker" sprocs in each database as in a TVP. The parameters indicate which set(s) of data to process and in which order. However, when I run the "master" sproc and it calles the fire "worker" it get the following error:

  Operand type clash: paramTbl is incompatible with paramTbl

  I have created the same table type in each database (it wouldn't even create the worker sprocs otherwise).

  Is there a limitation that prevents passing TVPs between sprocs in different databases?

  Thanks!

• jerry-621596

  SSCertifiable

  Points: 6096

  More actions

  July 19, 2010 at 10:19 am

  #1194993

  A little more research indicates that all types are DB specific and cannot cross DB boundaries.

  This article gives some helpful alternatives: http://www.sommarskog.se/share_data.html#prockeyed