4

I have a set of custom CLR functions installed in single database. When I need to use these functions in queries/views/procedures on another database, I simply reference them by three-part name [db].[schema].[function]() and it works fine.

But I can't use them in computed columns because of error 4120 A user-defined function name cannot be prefixed with a database name in this context.

Ofcourse, there's no problem with adding computed column on the same database using two-part name [schema].[function]().

Any ideas how to solve this problem? Maybe I can create some kind of "shortcut" functions in every other database that would reference "original" ones? Or could I install these functions in one of system databases in a way that will enable me to call function using only it's name, just like left() or substring()?

For now, the script that I use to update and install new functions, does the same thing on every database that I need the functions in, and in model db, too. But I wish there was more elegant way to do this.

AdamL
  • 12,421
  • 5
  • 50
  • 74

2 Answers2

2

There is no more elegant way to do this. A computed column cannot rely on an external function (even if you hide it behind a synonym, which seems to be the kind of "shortcut" function you're after) - the problem is that schema stability can't be broken by cross-database relationships - what happens when the database is offline, or single_user, or gets dropped? This is similar to the reason a foreign key can't reference a table in another database and a table can't belong to a schema in another database.

You already know that you can't use a 3-part name (in SQL Server 2014 I get a different error):

Msg 207, Level 16, State 1
Invalid column name 'other_database_name'.

If you try to mask your function with a synonym, you get:

Msg 2788, Level 16, State 2
Synonyms are invalid in a schemabound object or a constraint expression.

So, deploy your function to all of your databases, or pre-calculate the values and insert and store them manually instead of relying on the computed column.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thank you for thorough answer. It's a pain one can't create functions with server-wide scope. Meanwhile I had another idea: propagate drop/alter/create statements to other databases by DDL triggers, but this is very unsafe solution, at least in my case. – AdamL Sep 27 '13 at 09:24
1

I have used a workaround for some CONSTRAINT CHECKs, where I had the same error/problem. The logic should work for computed columns as well.

I encapsulate the custom function from the other database within a new function in the current database, which I name "cf_ref_SameFunctionName".
It is not perfect, but it enables you to have your logic in one place, even if you must create one wrapper for each function you need.

Pros:

  • Your logic is still in one place
  • You can alter the base function, since it is not locked up by a reference.

Cons:

  • The relationship is broken to the base function, with all the hazards this may lead to.
Jarle Bjørnbeth
  • 429
  • 3
  • 9