Menu
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?
321
To access the data from table which exists in different databases hosted in same SQL Server instance, you need to provide full qualify name for table such as table name including database name, and schema (database_name. schema_name. table_name) as shown below.
Create a user-defined table type that corresponds to the table that you want to populate. Pass the user-defined table to the stored procedure as a parameter. Inside the stored procedure, select the data from the passed parameter and insert it into the table that you want to populate.
Cross-database user-defined types seems to work only for CLR-based types. See this forum and MSDN (plus comments).
142
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
