CODEWITHSUNDEEP
sqlsql-server
Ramya
Ramya

Reputation: 230

Accessing User Defined Table type from another database

I need to access the user defined table type from another database.

I have used the following format:

DECLARE @Hierarchy AS [DatabaseName].[Schema].[Table]

I get the following error:

The type name '[DatabaseName].[Schema].[Table]' contains more than the maximum number of prefixes. The maximum is 1

How can I access the User Defined table type from another database?

Upvotes: 8

Views: 3820

Answers (2)

uğur döner
uğur döner

Reputation: 1

You can access it in dynamic Sql statement

"USE [DatabaseName] DECLARE @Hierarchy"

Upvotes: -2

TomTom
TomTom

Reputation: 62159

SImple: You can not.

Schema definitions must be from the same database - and a user defined type in your usage scenario is a schema definition.

This is why there is only max. 1 prefix allowed - 1 prefix identifies another schema (within the same database).

So, you will have to copy the type definition over to your other database to use it.

Upvotes: 10

Related Questions