CODEWITHSUNDEEP
sqlsql-servervariablessql-server-2012
nsivakr
nsivakr

Reputation: 1595

Referring to a table variable from a different database

Declaring a table variable with database name throws the following error.

The type name 'dbname.dbo.TableType' contains more than the maximum number of prefixes. The maximum is 1.

Declare @cutoffDtes as dbname.dbo.TableType

However, the same works when I do the following

use dbname
Declare @cutoffDtes as dbo.TableType

Is there a way to declare the variable along with database name?

Upvotes: 0

Views: 721

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The documentation is pretty clear (once you find the reference) that user defined types are available only within a single database:

Using UDTs Across Databases

UDTs are by definition scoped to a single database. Therefore, a UDT defined in one database cannot be used in a column definition in another database. In order to use UDTs in multiple databases, you must execute the CREATE ASSEMBLY and CREATE TYPE statements in each database on identical assemblies. Assemblies are considered identical if they have the same name, strong name, culture, version, permission set, and binary contents.

In other words, you can repeat the definition in other databases and if everything is the same, then they are compatible.

Upvotes: 2

Related Questions