Can not find function error

markog

When trying to run a package I receive the following error.

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.ufnGetFreeQtyForPODetail", or the name is ambiguous

---

In the table the field for the free quantity is defined as follows

[FreeQuantity] AS ([dbo].[ufnGetFreeQtyForPODetail]([PurchaseOrderID],[LineNumber])

---

The following is the function

USE [DemoData]
GO
/****** Object:  UserDefinedFunction [dbo].[ufnGetFreeQtyForPODetail]    Script Date: 04/14/2009 14:06:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufnGetFreeQtyForPODetail](@PurchaseOrderID [int],@LineNumber [int])
RETURNS decimal(18,4)

AS 
BEGIN
    DECLARE @ret decimal(18,4);
    DECLARE @recv decimal(18,4);
    DECLARE @alloc decimal(18,4);

    SELECT @recv = [ReceivedQty]
    FROM [dbo].PurchaseOrderDetail 
    WHERE PurchaseOrderID = @PurchaseOrderID
    AND LineNumber = @LineNumber;

    SELECT @alloc = SUM([AllocatedQty])
    FROM [dbo].SalesOrderPurchaseOrderAllocation 
    WHERE PurchaseOrderID = @PurchaseOrderID
    AND PurchaseOrderLineNumber = @LineNumber;

    IF (@recv IS NULL) 
        SET @recv = 0    
    IF (@alloc IS NULL) 
        SET @alloc = 0
       
    RETURN (@recv - @alloc)
END;


---------------

If I removed the calculated field function call, it works fine. I also have alot of similar functions being called in the same way.

Is there anything obvious that I am missing?

Chris Auckland

Thanks for your post.

The problem you have encounterd is with a bug with the SQL Compare engine used in SQL Packager 6. As you correctly idenetified, if you have a function in a table definition, it will incorrectly script the table before the function.

This has now been fixed in SQL Compare 8.0 engine, and will be included in the next SQL Packager release.

In the mean time, you could create the schema and data scripts using SQL Compare and SQL data Compare respecively, stitch them together manually, and then use SQL Packager to package the script.

I hope this is helpful.

dhochee

I encountered the same error using the trial of SQL Packager 6 in the SQL Toolbelt in the trial download which includes SQL Compare 8.1.

I can see that SQL Packager 6 uses the Ver 7.1 of RedGate.SQLCompare.Engine.dll (and related DLL's), even though the newer version of SQL Compare is installed. I can understand that this approach eases distribution and troubleshooting (don't have to worry about mixing and matching versions), but it also seems like a mistake that fixes and updates to one product don't benefit another dependent product.

Maybe it's dreaming, but it would sure be nice if the SQL Packager could use the latest installed version of SQL Compare (and Data Compare) engines. That would allow bug fixes and enhancements to work across product lines. I can't imagine the interfaces change so much that this would be difficult, although I can understand choosing to forgo that approach due to possible support issues.

Just wanted to toss that out there for consideration since I was surprised and disappointed when SQL Packager didn't work for me as hoped.

Chris Auckland

Thanks for your post.

We have an updated version of SQL Packager 6 that uses the updated assemblies if you wish to try it.

For more information and a download link click here

It would be nice if the release version of SQL Packager was always kept up to date with our other applications, but it is a standalone product and has it's own develoment cycle and can sometimes be left behind.