cross-database function call

Question

cross-database function call

rob.lewis 86087

Right there with Babe

Points: 798
More actions
July 2, 2015 at 6:23 am

#398683

i have 2 databases on the same server
One has a function, the other has the tables and views
using dba
select dbo.function(t.column) as x from dbb.dbo.table as t
gives m an invalid object name of dbo.table
using dba
select top 1 * from dbb.dbo.table works fine.
also if i create the function on the same db it works.
Does anyone know if they have stopped allowing cross database function calls or if there is a syntax error in this?
Can't say i use functions much so might be a bit out of touch.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

sgmunson SSC Guru Points: 110639 More actions · Answer 1

rob.lewis 86087 (7/2/2015)
i have 2 databases on the same server
One has a function, the other has the tables and views
using dba
select dbo.function(t.column) as x from dbb.dbo.table as t
gives m an invalid object name of dbo.table
using dba
select top 1 * from dbb.dbo.table works fine.
also if i create the function on the same db it works.
Does anyone know if they have stopped allowing cross database function calls or if there is a syntax error in this?
Can't say i use functions much so might be a bit out of touch.

My gut says database ownership chaining is having some kind of impact here, but I don't have the familiarity with exactly how it works...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

PhilPacha Hall of Fame Points: 3645 More actions · Answer 2

Since you are referencing two databases, you have to specify the database name for the object (function or table) which is in the database you are NOT running against. In your example, you're not mentioning either database, so both objects are assumed to be in the same database, i.e., the one you're running against.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

rob.lewis 86087 (7/2/2015)
i have 2 databases on the same server
One has a function, the other has the tables and views
using dba
select dbo.function(t.column) as x from dbb.dbo.table as t
gives m an invalid object name of dbo.table
using dba
select top 1 * from dbb.dbo.table works fine.
also if i create the function on the same db it works.
Does anyone know if they have stopped allowing cross database function calls or if there is a syntax error in this?
Can't say i use functions much so might be a bit out of touch.

According to the code you posted, the two functions are actually different. In the first case, it's a scalar function. In the second case, it's either an mTVF (multi-statement Table Valued Function) or an iTVF (inline Table Valued Function).

Scalar functions must be called in the SELECT clause of a query as you have in your first query. The other two types must be called from the FROM clause of a query.

Again, it looks like you have two different types of functions that just happen to be the same name.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

I suspect Jeff is right and your function is a (i)TVF in database "dba", so needs to be referenced in the FROM clause, not the SELECT.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);