Bug found? Cannot alter function referenced by table

Tue, 30 Jun 2009 16:18:36 Z

hI, I generated a schema compare script and when I executed the script I got this error, which is:

The system is trying to alter a function, but that function is used by a table, it seems the script is not correctly generated, as far as I know it should alter first the function and then alter the column of the table that uses that function

Or maybe this is not a bug from the product and its happening by something else?

Msg 3729, Level 16, State 3, Procedure CalcularCaudalPromedioLADCaudalesLodosActivados, Line 18
Cannot ALTER 'ROP.CalcularCaudalPromedioLADCaudalesLodosActivados' because it is being referenced by object 'CaudalesLodosActivados'.

MCPD ENTERPRISE APPLICATIONS DEVELOPER http://wantmvp.blogspot.com/

Bug found? Cannot alter function referenced by table

Tue, 30 Jun 2009 19:07:53 Z

If you are using the GDR release make sure to uncheck the Schema Compare option "Enforce only minimal dependencies".

GertD @ www.DBProj.com

Bug found? Cannot alter function referenced by table

Tue, 30 Jun 2009 19:23:17 Z

Its unchecked.

Let me explain you what I have found in detail

We have these errors:

Msg 207, Level 16, State 1, Procedure CalcularMasaDesecharCaudalesLodosActivados, Line 8
Invalid column name 'totalMasaEnReactores'

The script of the function is:

ALTER FUNCTION [ROP].[CalcularMasaDesecharCaudalesLodosActivados]
(@idCaudalLodoActivado DECIMAL (30, 4))
RETURNS INT
AS
BEGIN
    declare @masaDesechar decimal(30,4)

    select @masaDesechar = totalMasaEnReactores / edadLodos
    from [ROP].CaudalesLodosActivados
    where idCaudalLodoActivado = @idCaudalLodoActivado
    return @masaDesechar
END

The field in bold is not being found, so I made a search and I found that its dropping that column in the script:

ALTER TABLE [ROP].[CaudalesLodosActivados] DROP COLUMN [totalMasaEnReactores];

I have more or less 10 errors like this one, and all seems to be by the same thing, the columns are being deleted and then the function is being altered.

I also found this error, it seems that its something for renaming tables.

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

BEGIN TRANSACTION;

CREATE TABLE [ROP].[tmp_ms_xx_CaudalesLodosActivados] (
    [idCaudalLodoActivado]              INT            IDENTITY (1, 1) NOT NULL,
    [idCaudalLodoActivadoEncabezado]    INT            NOT NULL,
    [hora]                              TINYINT        NOT NULL,
    [totalMasaEnReactores]              AS             ([ROP].[CalcularTotalMasaEnReactoresCaudalesLodosActivados]([idCaudalLodoActivado])),
    [solidosPromedioSedimentacionFinal] AS             ([ROP].[CalcularSolidosPromedioSedimentacionFinalCaudalesLodosActivados]([idCaudalLodoActivado])),
    [qQ]                                AS             ([ROP].[CalcularqDCaudalesLodosActivados]([idCaudalLodoActivado])),
    [edadLodos]                         SMALLINT       NOT NULL,
    [caudalPromedioLAD]                 AS             ([ROP].[CalcularCaudalPromedioLADCaudalesLodosActivados]([idCaudalLodoActivado])),
    [concentracionLAR]                  AS             ([ROP].[CalcularConcentracionLARCaudalesLodosActivados]([idCaudalLodoActivado])),
    [edadLodosReal]                     AS             ([ROP].[CalcularEdadLodosRealCaudalesLodosActivados]([idCaudalLodoActivado])),
    [masaDesechar]                      AS             ([ROP].[CalcularMasaDesecharCaudalesLodosActivados]([idCaudalLodoActivado])),
    [caudalBombaTFP]                    AS             ([ROP].[CalcularCaudalBombaTFPCaudalesLodosActivados]([idCaudalLodoActivado])),
    [horasBombear]                      DECIMAL (6, 2) NOT NULL,
    [usuario]                           VARCHAR (50)   NOT NULL
);

ALTER TABLE [ROP].[tmp_ms_xx_CaudalesLodosActivados]
    ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_CaudalesLodosActivados] PRIMARY KEY CLUSTERED ([idCaudalLodoActivado] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

IF EXISTS (SELECT TOP 1 1
           FROM   [ROP].[CaudalesLodosActivados])
    BEGIN
        SET IDENTITY_INSERT [ROP].[tmp_ms_xx_CaudalesLodosActivados] ON;
        INSERT INTO [ROP].[tmp_ms_xx_CaudalesLodosActivados] ([idCaudalLodoActivado], [idCaudalLodoActivadoEncabezado], [hora], [edadLodos], [horasBombear], [usuario])
        SELECT   [idCaudalLodoActivado],
                 [idCaudalLodoActivadoEncabezado],
                 [hora],
                 [edadLodos],
                 [horasBombear],
                 [usuario]
        FROM     [ROP].[CaudalesLodosActivados]
        ORDER BY [idCaudalLodoActivado] ASC;
        SET IDENTITY_INSERT [ROP].[tmp_ms_xx_CaudalesLodosActivados] OFF;
    END

DROP TABLE [ROP].[CaudalesLodosActivados];

EXECUTE sp_rename N'[ROP].[tmp_ms_xx_CaudalesLodosActivados]', N'CaudalesLodosActivados';

EXECUTE sp_rename N'[ROP].[tmp_ms_xx_clusteredindex_PK_CaudalesLodosActivados]', N'PK_CaudalesLodosActivados', N'OBJECT';

COMMIT TRANSACTION;

MCPD ENTERPRISE APPLICATIONS DEVELOPER http://wantmvp.blogspot.com/

Bug found? Cannot alter function referenced by table

Tue, 30 Jun 2009 19:54:38 Z

I found that after the script REMOVES the column, it doesnt add it again anywhere else.

MCPD ENTERPRISE APPLICATIONS DEVELOPER http://wantmvp.blogspot.com/

Bug found? Cannot alter function referenced by table

Wed, 01 Jul 2009 17:10:22 Z

Luis,

This sounds like it might be a bug. A few questions for you:

* which version of Visual Studio Database Edition do you have?

* are you excluding any object types from the schema comparison (either manually, or through schema compare options)?

* are you comparing a database project to a database?

* are there a lot of differences between the source and target, and if not, can you list them here?

Hopefully we can help you through this.

-Genevieve Orchard (VSTS Database Edition Test Team)

Bug found? Cannot alter function referenced by table

Wed, 01 Jul 2009 22:24:43 Z

Hello

1. I am using the latest version. GDR RC I think
2. Nop, I am using all default options.
3. Yes, what I do is: create a database project, then import a database, then on project properties I set the target database, then I build and deploy and it generates a long script.
4. There are many differences but the main problem I have detected is because of this:

We have tables with computed columns, those computed columns use a function. That function changed. So the script generated a DROP COLUMN. Then it generated an strange script in which it creates a temporal table, and then it drops the old table and rename the tempo one to the name it should have.

I took yesterday 4 hours, splitting the generated script into pieces until I found the error: and I had to fix it this way.

DROP the table, Alter the function. create the temp table, and then rename it.

Oh, another thing I found, its that some of the functions used in its computations columns from the same table that the script had already removed in the first steps of the script.

This is one part of the script that I modified to make it work. However you must know that those tables were empty, so I hadnt to do any additional steps.
The generated scripts of the product shoud do in this case:

1. Create a temporary table
2. Copy the data from the table to the temporary table.
3. Drop the table
4. Create or alter the functions that dont depend on other columns of the same table.
5. Alter the functions that depend on the same table
6. Rename the temporary the table to the name it shoud have.

Just my toughts, maybe I amw ron in step 5, because it might be confusing.

GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

drop table [rop].ConsumoSal
GO

ALTER FUNCTION [ROP].[CalcularconsumoNaClTotalConsumoSal]
(@idConsumoSal DECIMAL (30, 2))
RETURNS BIGINT
AS
BEGIN
	DECLARE @resultado decimal(30,2)

	SELECT  @resultado = isnull(consumoNaClBT1,0) + isnull(consumoNaClBT2,0)
	FROM ConsumoSal 
    WHERE idConsumoSal =@idConsumoSal

	return @resultado
END
GO

GO
BEGIN TRANSACTION;

CREATE TABLE [ROP].[tmp_ms_xx_ConsumoSal] (
    [idConsumoSal]            INT          IDENTITY (1, 1) NOT NULL,
    [idPlanta]                INT          NULL,
    [NroBomba]                INT          NULL,
    [PorcentajeBP]            DECIMAL (18) NULL,
    [PorcentajeStroke]        AS           ([ROP].CalcularPorcentajeStrokeConsumoSal([idConsumoSal])),
    [FlujoBP]                 AS           ([ROP].[CalcularFlujoBPConsumoSal]([idConsumoSal])),
    [InicioFechaInicial]      DATETIME     NULL,
    [IniciohoraInicial]       VARCHAR (50) NULL,
    [FinFechaFinal]           DATETIME     NULL,
    [FinHoraFinal]            VARCHAR (5)  NULL,
    [TiempoHoras]             AS           (datediff(minute,[inicioFechaInicial],isnull([finFechaFinal],getdate()))/(60.0)),
    [ConcentracionBT1]        DECIMAL (18) NULL,
    [ConsumoNaClBT1]          AS           ([ROP].[CalcularConsumoNaClBT1ConsumoSal]([idConsumoSal])),
    [ConcentracionBT2]        DECIMAL (18) NULL,
    [ConsumoNaClBT2]          AS           ([ROP].[CalcularConsumoNaClBT2ConsumoSal]([idConsumoSal])),
    [ConsumoNaClTotal]        AS           ([ROP].[CalcularconsumoNaClTotalConsumoSal]([idConsumoSal])),
    [ConcentracionInicialSRP] DECIMAL (18) NULL,
    [ConcentracionFinalSRP]   DECIMAL (18) NULL,
    [usuario]                 VARCHAR (50) NULL
);

ALTER TABLE [ROP].[tmp_ms_xx_ConsumoSal]
    ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_ConsumoSal] PRIMARY KEY CLUSTERED ([idConsumoSal] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

--IF EXISTS (SELECT TOP 1 1
--           FROM   [ROP].[ConsumoSal])
--    BEGIN
--        SET IDENTITY_INSERT [ROP].[tmp_ms_xx_ConsumoSal] ON;
--        INSERT INTO [ROP].[tmp_ms_xx_ConsumoSal] ([idConsumoSal], [idPlanta], [nroBomba], [PorcentajeBP], [inicioFechaInicial], [inicioHoraInicial], [finFechaFinal], [finHoraFinal], [concentracionBT1], [concentracionBT2], [ConcentracionInicialSRP], [ConcentracionFinalSRP], [usuario])
--        SELECT   [idConsumoSal],
--                 [idPlanta],
--                 [nroBomba],
--                 [PorcentajeBP],
--                 [inicioFechaInicial],
--                 [inicioHoraInicial],
--                 [finFechaFinal],
--                 [finHoraFinal],
--                 [concentracionBT1],
--                 [concentracionBT2],
--                 [ConcentracionInicialSRP],
--                 [ConcentracionFinalSRP],
--                 [usuario]
--        FROM     [ROP].[ConsumoSal]
--        ORDER BY [idConsumoSal] ASC;
--        SET IDENTITY_INSERT [ROP].[tmp_ms_xx_ConsumoSal] OFF;
--    END

--DROP TABLE [ROP].[ConsumoSal];

EXECUTE sp_rename N'[ROP].[tmp_ms_xx_ConsumoSal]', N'ConsumoSal';

EXECUTE sp_rename N'[ROP].[tmp_ms_xx_clusteredindex_PK_ConsumoSal]', N'PK_ConsumoSal', N'OBJECT';

COMMIT TRANSACTION;



-- Fin Parte6 --

Please contact me via email and I can send you the full script, and I can even send you the 2 databases compared, I am sure with my help we can make this error to be solved for the next release.

lvalencia at intergrupo dot com
or
le.valencia at hotmail dot com

MCPD ENTERPRISE APPLICATIONS DEVELOPER http://wantmvp.blogspot.com/

Bug found? Cannot alter function referenced by table

Thu, 02 Jul 2009 23:20:29 Z

I will follow up with Luis offline.

-Genevieve Orchard (VSTS Database Edition Test Team)

Bug found? Cannot alter function referenced by table

Thu, 08 Oct 2009 18:58:03 Z

You probably already resolved this, but I found a simple solution and others might find it useful if not obvious.

Create an SQL function that calls your original function very simply, for example, let's say you had a function that converted numbers to linguistic text named "NumToText", as I had when I stumbled upon this situation, and this function was used as a formula for a computed column specification for field tbl1.myNumToTextFld. Now you can't modify NumToText without messing with any table/field elements that are referring to it. Instead of having to mess with that, have myNumToTextFld refer to the new "NumToText_Ref" function, which simply calls NumToText. Now you can modify NumToText all you wish and with complaints since it's not directly referenced by the computed column function spec.

The formula for the field myNumToTextFld would look like:
([dbo].[NumToText_Ref](isnull([myNumFld],(0))))

And you create a new function

ALTER

FUNCTION [dbo].[NumToText_Ref] (@num decimal(18,7))

/* exists solely so it can be referenced by a calculated field in table myTbl and the underlying functions can be recompiled

without having to drop table columns */

RETURNS

nchar(256)

BEGIN

RETURN dbo.NumToText(@num) /* Now you can modify NumToText all you wish */

END