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 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.
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy