Can not find function error

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Can not find function error

Postby markog » Tue Apr 14, 2009 2:16 pm

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?
markog
 
Posts: 1
Joined: Tue Apr 14, 2009 12:58 pm

Postby Chris Auckland » Fri Apr 17, 2009 1:00 pm

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.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby dhochee » Wed Mar 31, 2010 5:38 am

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.
dhochee
 
Posts: 1
Joined: Wed Mar 31, 2010 3:45 am

Postby Chris Auckland » Wed Mar 31, 2010 12:47 pm

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.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 0 guests