How to use the data type (table) defined in another database in SQL2k8?

Question

I have a table type defined in the database. It is used as a parameter stored in a table in a stored procedure. I would like to call this procedure from another database, and in order to pass the parameter, I need to refer to this specific type.

But when I do DECLARE @table dbOtherDatabase.dbo.TypeName , it tells me that The type name 'dbOtherDatabase.dbo.TypeName' contains more than the maximum number of prefixes. The maximum is 1. The type name 'dbOtherDatabase.dbo.TypeName' contains more than the maximum number of prefixes. The maximum is 1.

How can I refer to this type of table?

Answer 1

User-defined cross-database types seem to work only for CLR types. See this forum and MSDN (plus comments).

Answer 2

Can you not just determine the type in both databases.

Edit

See this article on how to do what you need.

Answer 3

you can try using sp_executesql:

 DECLARE @mylist integer_list_tbltype, @sql nvarchar(MAX) SELECT @sql = N'SELECT p.ProductID, p.ProductName FROM Northwind..Products p WHERE p.ProductID IN (SELECT n FROM @prodids)' INSERT @mylist VALUES(9),(12),(27),(37) EXEC sp_executesql @sql, N'@prodids integer_list_tbltype READONLY', @mylist 

and if this does not work, you may need to create a wrapper procedure in the remote database, where you pass the CSV string, and the shell procedure will split it and create a table (now using the type of the local table), so that we can then proceed to the actual procedure. See this answer for an explanation of how to split a CVS string.

Answer 4

So far for this topic, but I tried to do the same and was annoyed by the same restriction.

Do not declare the table @, but use table #, which can be called from the exec line, where the database can be switched to one that has the table type.

eg.

 use dbA create type ThisTableRecord as table (id int, value varchar(max)) go create procedure ThisTableSave @ThisTable ThisTableRecord readonly AS begin select * from @thisTable end go use dbB go create procedure ThatTableSave as begin create table #thatTable (id int, value varchar(max)) insert into #thatTable values (1, 'killing') , (2, 'joke') , (3, 'the') , (4, 'damned') exec (' use dbA declare @thisTable thisTableRecord insert into @thisTable select * from #thatTable exec thisTableSave @thisTable ') end exec ThatTableSave