SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


On the same server, can you use a TABLE-valued user-defined function from another database?


On the same server, can you use a TABLE-valued user-defined function from another database?

Author
Message
saintor1
saintor1
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 108
I did search but could find a specific answer. I can use another db's scalar function, but for whatever reason, even if I put [database].[dbo].[TableFunctionName] for a table function, I get an error.

SELECT    InvWarehouse.StockCode, InvWarehouse.Warehouse, InvWarehouse.YtdQtySold, InvWarehouse.PrevYearQtySold, InvWarehouse.UnitCost, InvMaster.MinPricePct, 
InvPrice.PriceCode, InvPrice.SellingPrice, InvMaster.ProductClass, InvMaster.PriceCategory, InvMaster.PriceMethod, [W_SE-MFG].[dbo].[LastRecDate_tvf](InvMaster.StockCode, InvWarehouse.Warehouse, 'K')
FROM InvMaster INNER JOIN
InvWarehouse ON InvMaster.StockCode = InvWarehouse.StockCode AND InvMaster.WarehouseToUse = InvWarehouse.Warehouse INNER JOIN
InvPrice ON InvMaster.StockCode = InvPrice.StockCode AND InvMaster.StockCode = InvPrice.StockCode


Error:

Msg 4121, Niveau 16, État 1, Ligne 1
Cannot find either column "W_SE-MFG" or the user-defined function or aggregate "W_SE-MFG.dbo.LastRecDate_tvf", or the name is ambiguous.

Thom A
Thom A
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82427 Visits: 21114
No, types are database bound, and can only be referenced within that database. For example, on my environment:
USE Sandbox;
GO
--This fails
DECLARE @test Assets.app.OrderItems;
GO

USE Assets;
GO
--This works
DECLARE @test app.OrderItems;
GO


If 2 or more of your databases both need to be able to use a Custom Data Type, you'll need to create it on both databases. If all of them do, then you'll need to create it on all of them (and I suggest you create it on the model database to, for future databases).


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)

Group: General Forum Members
Points: 391306 Visits: 42828
I think it depends. I have Jeff's delimited split functions in a DBAUtilities database and I can use them in other databases on my laptop. It would help if posted the DDL for the function.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Thom A
Thom A
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82427 Visits: 21114
Lynn Pettis - Wednesday, January 3, 2018 9:08 AM
I think it depends. I have Jeff's delimited split functions in a DBAUtilities database and I can use them in other databases on my laptop. It would help if posted the DDL for the function.


I think I misunderstoof the OP's original question.. (blame the fact it's the first day back after the holidays). Thought theyw ere talking about User defined data types, not function. /facepalm.

Yes, Lynn is right, you can easily refer to functions on other databases (I do this a lot, having a application database I can't amend, thus have a separate database for user functions which relate to it). Apologies for the confusion.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Chris Harshman
Chris Harshman
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36988 Visits: 7049
it looks like you're trying to reference the table valued function as you would a scalar function, by putting it in the SELECT clause. It would work better to APPLY to it in your FROM clause like:
SELECT InvWarehouse.StockCode, InvWarehouse.Warehouse, InvWarehouse.YtdQtySold, InvWarehouse.PrevYearQtySold, InvWarehouse.UnitCost, InvMaster.MinPricePct, 
InvPrice.PriceCode, InvPrice.SellingPrice, InvMaster.ProductClass, InvMaster.PriceCategory, InvMaster.PriceMethod, lrd.columnname
FROM InvMaster
INNER JOIN InvWarehouse ON InvMaster.StockCode = InvWarehouse.StockCode AND InvMaster.WarehouseToUse = InvWarehouse.Warehouse
INNER JOIN InvPrice ON InvMaster.StockCode = InvPrice.StockCode AND InvMaster.StockCode = InvPrice.StockCode
CROSS APPLY [W_SE-MFG].[dbo].[LastRecDate_tvf](InvMaster.StockCode, InvWarehouse.Warehouse, 'K') lrd

Chris Harshman
Chris Harshman
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36988 Visits: 7049
Also, FYI, depending on your database nomenclature, it may be better to create a synonym to this function instead of using a 3 part object name [db_name].[schema_name].[function_name]

That way your queries can always reference the same name (the synonym) regardless of where the function actually lives.
https://docs.microsoft.com/en-us/sql/relational-databases/synonyms/synonyms-database-engine
saintor1
saintor1
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 108
Chris Harshman - Wednesday, January 3, 2018 10:49 AM
it looks like you're trying to reference the table valued function as you would a scalar function, by putting it in the SELECT clause. It would work better to APPLY to it in your FROM clause like:
SELECT InvWarehouse.StockCode, InvWarehouse.Warehouse, InvWarehouse.YtdQtySold, InvWarehouse.PrevYearQtySold, InvWarehouse.UnitCost, InvMaster.MinPricePct, 
InvPrice.PriceCode, InvPrice.SellingPrice, InvMaster.ProductClass, InvMaster.PriceCategory, InvMaster.PriceMethod, lrd.columnname
FROM InvMaster
INNER JOIN InvWarehouse ON InvMaster.StockCode = InvWarehouse.StockCode AND InvMaster.WarehouseToUse = InvWarehouse.Warehouse
INNER JOIN InvPrice ON InvMaster.StockCode = InvPrice.StockCode AND InvMaster.StockCode = InvPrice.StockCode
CROSS APPLY [W_SE-MFG].[dbo].[LastRecDate_tvf](InvMaster.StockCode, InvWarehouse.Warehouse, 'K') lrd

Yeah I noticed it and that was my main error. Thanks

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search