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, Mar 06 '12 at 10:33
@vulkanino I can, but as I have seen, they are not the same type — Bauer, Mar 06 '12 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, Feb 18 '13 at 22:43

score 6 · Accepted Answer · edited May 23 '17 at 12:17

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 '17 at 12:17

Community

1
1

answered Mar 06 '12 at 13:24

Damien_The_Unbeliever

234,701
27
340
448

3

Unfortunately this is not the answer I wanted to hear. Thank you – Bauer Apr 23 '12 at 07:47
@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? – Kulingar Jul 31 '13 at 23:24
@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). – Damien_The_Unbeliever Aug 01 '13 at 05:39
@Damien_The_Unbeliever Thanks for the answer that keeps on giving, this has been causing me some headaches the last hour. – conroyrw Apr 10 '15 at 14:45

score 5 · Answer 2 · edited Aug 17 '14 at 18:15

5

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.

edited Aug 17 '14 at 18:15

answered Mar 06 '12 at 12:50

Vikram

8,235
33
47

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. – Bauer Mar 06 '12 at 14:44

User defined TYPE across databases

2 Answers2

Linked