How can we help you today? How can we help you today?

Rerunnable scripts dropping functions in incorrect order

I am trying to create a re-runnable deployment script that requires modifying function1 (fnMain) and dropping function2 (fnSub) that calls function1 (fnMain).

This dependency is not being picked up and the resultant script fails as it is trying to drop function 1 before function2.  Currently using version 14.0.7.13517 of SQL compare against SQL server 2014.

The script below is a simple test to reproduce the issue.

USE master

IF EXISTS( SELECT * FROM sys.databases AS d WHERE d.name = 'Test1')
    DROP DATABASE Test1;
GO
IF EXISTS( SELECT * FROM sys.databases AS d WHERE d.name = 'Test2')
    DROP DATABASE Test2;
GO

CREATE DATABASE Test1
GO
USE Test1
GO
CREATE FUNCTION [dbo].[fnMain]()
RETURNS VARCHAR(MAX) WITH SCHEMABINDING
AS
BEGIN
    DECLARE @ret VARCHAR(MAX);
    RETURN @ret;
END
GO


CREATE DATABASE Test2
GO
USE Test2
GO
CREATE FUNCTION [dbo].[fnMain]()
RETURNS VARCHAR(MAX) WITH SCHEMABINDING
AS
BEGIN
    DECLARE @ret VARCHAR(MAX) = '';
    RETURN @ret;
END

GO

CREATE FUNCTION [dbo].[fnSub]()
RETURNS VARCHAR(MAX) WITH SCHEMABINDING
AS
BEGIN
    RETURN dbo.fnMain();
END
GO

USE master
GO
Use SQL compare to deploy from Test1 to Test2 (with the Add DROP and CREATE for rerunnable scripts checked) and the script will fail with the error cannot drop function dbo.fnMain because it is being referenced by object fnSub


pblack
0

Comments

1 comment

Sort by
  • GregE
    I have a similar issue, in which a sproc is being modified to use a new column in a table, before that column has been added to the table. Moving the ALTER TABLE above the sproc update in the script SQL Compare has generated fixes it. SQL Compare is great at resolving other dependencies, why not this? Using SQL Compare 14.0.7.13517 (latest) and SQL Server 2012 databases.

    EDIT: I've discovered why this table is special in my case - it's not an ALTER TABLE, it's rebuilding the table using a temporary recovery table. I'll create a separate thread for that.
    GregE
    0

Add comment

Please sign in to leave a comment.