January 3, 2018 at 8:15 am
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.
January 3, 2018 at 8:20 am
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-2 Assets.app.OrderItems;
GO
USE Assets;
GO
--This works
DECLARE @test-2 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.
Larnu.uk
January 3, 2018 at 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.
January 3, 2018 at 9:16 am
Lynn Pettis - Wednesday, January 3, 2018 9:08 AMI 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.
Larnu.uk
January 3, 2018 at 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
January 3, 2018 at 10:52 am
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
January 3, 2018 at 12:16 pm
Chris Harshman - Wednesday, January 3, 2018 10:49 AMit 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply