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

sql-server-2008 user-defined-types
Victor Rodrigues picture Victor Rodrigues · May 20, 2010 · Viewed 8.9k times · Source

I have a Table Type defined in a database. It is used as a table-valued parameter in a stored procedure. I would like to call this procedure from another database, and in order to pass the parameter, I need to reference this defined 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.

How could I reference this table type?

Answer

devio picture devio · May 20, 2010

Cross-database user-defined types seems to work only for CLR-based types. See this forum and MSDN (plus comments).

Related questions

How to check existence of user-define table type in SQL Server 2008?

I have a user-defined table type. I want to check it's existence before editing in a patch using OBJECT_ID(name, type) function. What type from the enumeration should be passed for user-defined table types? N'U' like for user defined …

Read More
The EXECUTE permission is denied on the user-defined table types?

I have a question about User-Defined Table Types in SQL Server 2008. For the need of one of the ASP.NET application we defined our own table-types on SQL Server 2008 to use them as parameters in the stored procedures (when executing …

Read More
SQL Server 2008 - How do i return a User-Defined Table Type from a Table-Valued Function?

Here's my user-defined table type... CREATE TYPE [dbo].[FooType] AS TABLE( [Bar] [INT], ) This is what ive had to do in my table-valued function to return the type: CREATE FUNCTION [dbo].[GetFoos] RETURN @FooTypes TABLE ([Bar] [INT]) INSERT INTO @FooTypes (1) …

Read More