User defined TYPE across databases

Question

I have a database that holds common functions that I use across multiple databases. One of these functions takes in a table as a parameter which is a user defined TYPE. I would like to know if there is a way to call this function from another database.

I tried to define the type in the other database like so:

DECLARE @bits as Common.dbo.Bits

However I received an error too many prefixes

I have tried adding the TYPE to each database, and then passing a table of that type to the function in the common database, but there I get an error of

Operand type clash: Bits is incompatible with Bits

can't you create all the needed types in the other databases, as part of the db first-setup process? — vulkanino
– vulkanino, Commented Mar 6, 2012 at 10:33
@vulkanino I can, but as I have seen, they are not the same type — Bauer
– Bauer, Commented Mar 6, 2012 at 14:46
It's funny, almost a year later and I came back to this same error. This time it was different schemas — Bauer
– Bauer, Commented Feb 18, 2013 at 22:43

Community · Accepted Answer · 2017-05-23 12:17:05Z

6

You can't. Even if you declare the type identically in two databases, they're not treated the same. And the DECLARE statement is only allowed a schema name and an object name, so there's no way to reference a type from another database.

See also this question for some possible work arounds (if you control both databases involved)

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Mar 6, 2012 at 13:24

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bauer Over a year ago

Unfortunately this is not the answer I wanted to hear. Thank you

Kulingar Over a year ago

@Damien_The_Unbeliever Isn't this strange of Microsoft to do? It's kind of like saying that in C# you can't use a class from a file unless it's on a file inside your current directory?

Damien_The_Unbeliever Over a year ago

@Kulingar - if you're working in two different assemblies, and you create two types with identical (full) names and members, you still can't substitute one for the other (directly).

conroyrw Over a year ago

@Damien_The_Unbeliever Thanks for the answer that keeps on giving, this has been causing me some headaches the last hour.

Vikram · Accepted Answer · 2014-08-17 18:15:47Z

According to Creating User-Defined Data Types:

If a user-defined data type is created in the model database, it exists in all new user-defined databases. However, if the data type is created in a user-defined database, the data type exists only in that user-defined database.

That's true about the Model database, but I don't think that they are the same type. Besides, I am working with existing databases.

Collectives™ on Stack Overflow

User defined TYPE across databases

2 Answers 2

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Linked

Related