SQL Compare ignores user defined function
nate.johnson
Posts: 7
I have a table valued function with changes that keeps getting ignored by SQL Compare. The changes are as follows:
The new function:
CREATE FUNCTION [dbo].[GetPricingTableEntryForPricePlanCostItemRules] (@pricePlanID [uniqueidentifier], @costItemID [uniqueidentifier], @accountID [uniqueidentifier], @costCurrencyID [uniqueidentifier], @cost [numeric] (14, 4))
RETURNS TABLE (
[CostItemEntitlementTypeID] [uniqueidentifier] NULL,
[RuleName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PriceMarkupType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PricingValue] [decimal] (16, 6) NULL,
[ChargebackMarkupType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChargebackValue] [decimal] (16, 6) NULL,
[SupplyOrderQuantityLimit] [int] NULL,
[Price] [decimal] (16, 6) NULL,
[ChargebackPrice] [decimal] (16, 6) NULL)
WITH EXECUTE AS CALLER
EXTERNAL NAME [XXXXXXXXX].[XXXXXXXX.XXXXXXXXX].[GetPricingTableEntryForPricePlanCostItemRules]
The old function:
CREATE FUNCTION [dbo].[GetPricingTableEntryForPricePlanCostItemRules] (@pricePlanID [uniqueidentifier], @costItemID [uniqueidentifier], @accountID [uniqueidentifier], @costCurrencyID [uniqueidentifier], @cost [numeric] (14, 4))
RETURNS TABLE (
[CostItemEntitlementTypeID] [uniqueidentifier] NULL,
[RuleName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PriceMarkupType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PricingValue] [decimal] (14, 4) NULL,
[ChargebackMarkupType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChargebackValue] [decimal] (14, 4) NULL,
[SupplyOrderQuantityLimit] [int] NULL,
[Price] [decimal] (14, 4) NULL,
[ChargebackPrice] [decimal] (14, 4) NULL)
WITH EXECUTE AS CALLER
EXTERNAL NAME [XXXXX].[XXXXXXX.XXXXXXXXX].[GetPricingTableEntryForPricePlanCostItemRules]
You can see the only changes are on the sizes of the decimal columns. (I have changed the external names to protect the innocent.) This function is being ignored, however. I have turned off all ignore options in an effort to get this recognized.
Could it be that the name of the function is too long? If so, then is this a bug?
Thanks,
Nate
The new function:
CREATE FUNCTION [dbo].[GetPricingTableEntryForPricePlanCostItemRules] (@pricePlanID [uniqueidentifier], @costItemID [uniqueidentifier], @accountID [uniqueidentifier], @costCurrencyID [uniqueidentifier], @cost [numeric] (14, 4))
RETURNS TABLE (
[CostItemEntitlementTypeID] [uniqueidentifier] NULL,
[RuleName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PriceMarkupType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PricingValue] [decimal] (16, 6) NULL,
[ChargebackMarkupType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChargebackValue] [decimal] (16, 6) NULL,
[SupplyOrderQuantityLimit] [int] NULL,
[Price] [decimal] (16, 6) NULL,
[ChargebackPrice] [decimal] (16, 6) NULL)
WITH EXECUTE AS CALLER
EXTERNAL NAME [XXXXXXXXX].[XXXXXXXX.XXXXXXXXX].[GetPricingTableEntryForPricePlanCostItemRules]
The old function:
CREATE FUNCTION [dbo].[GetPricingTableEntryForPricePlanCostItemRules] (@pricePlanID [uniqueidentifier], @costItemID [uniqueidentifier], @accountID [uniqueidentifier], @costCurrencyID [uniqueidentifier], @cost [numeric] (14, 4))
RETURNS TABLE (
[CostItemEntitlementTypeID] [uniqueidentifier] NULL,
[RuleName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PriceMarkupType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PricingValue] [decimal] (14, 4) NULL,
[ChargebackMarkupType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChargebackValue] [decimal] (14, 4) NULL,
[SupplyOrderQuantityLimit] [int] NULL,
[Price] [decimal] (14, 4) NULL,
[ChargebackPrice] [decimal] (14, 4) NULL)
WITH EXECUTE AS CALLER
EXTERNAL NAME [XXXXX].[XXXXXXX.XXXXXXXXX].[GetPricingTableEntryForPricePlanCostItemRules]
You can see the only changes are on the sizes of the decimal columns. (I have changed the external names to protect the innocent.) This function is being ignored, however. I have turned off all ignore options in an effort to get this recognized.
Could it be that the name of the function is too long? If so, then is this a bug?
Thanks,
Nate
Comments
You're right, it is a bug (although I don't think the length of the function name is a factor, it is more likely that the data type is not being picked up in table valued functions).
Sorry if it's causing you problems in the current version - I've added it to our list and we'll look into fixing it.
If you have a valid Support & upgrades option, you can download SQL Compare V.7 using the 'Check for updates' mechanism (SQL Compare GUI ->Help ->Check for updates)
or download using this link: ftp://ftp.red-gate.com/SQLToolbelt.zip
SQL Compare V.7 will install along side any previous versions of the software.
Many Thanks
Eddie Davis
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com