Red Gate forums :: View topic - Can not find function error
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

Can not find function error

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
markog



Joined: 14 Apr 2009
Posts: 1

PostPosted: Tue Apr 14, 2009 2:16 pm    Post subject: Can not find function error Reply with quote

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?
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Fri Apr 17, 2009 1:00 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
dhochee



Joined: 31 Mar 2010
Posts: 1

PostPosted: Wed Mar 31, 2010 5:38 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Wed Mar 31, 2010 12:47 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group