Script generated for objects with complex dependencies

Topics: Database Publishing Wizard
Dec 22, 2006 at 8:01 AM
Database Publishing Wizard 1.0 RC (version 0.3.0.0) generates an incorrect script if the database contains some dependecies that seem to be circular (but are not). For example, execute the following script:

CREATE DATABASE TestDatabase
GO
USE TestDatabase
CREATE TABLE T (X INT PRIMARY KEY)
GO
CREATE FUNCTION F() RETURNS INT AS BEGIN
RETURN (SELECT COUNT(*) FROM T)
END
GO
ALTER TABLE T ADD DEFAULT DBO.F() FOR X

Database Publishing Wizard 1.0 RC generates the following script:

/****** Object: UserDefinedFunction dbo.F Script Date: 12/22/2006 09:14:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE objectid = OBJECTID(N'dbo.F') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.F
GO
/****** Object: Table dbo.T Script Date: 12/22/2006 09:14:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE objectid = OBJECTID(N'dbo.T') AND type in (N'U'))
DROP TABLE dbo.T
GO
/****** Object: Table dbo.T Script Date: 12/22/2006 09:14:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE objectid = OBJECTID(N'dbo.T') AND type in (N'U'))
BEGIN
CREATE TABLE dbo.T(
Xint NOT NULL CONSTRAINT DF__T__X__7E6CC920 DEFAULT (DBO.F()),
CONSTRAINT PK__T__7C8480AE PRIMARY KEY CLUSTERED
(
X ASC
)WITH (PADINDEX = OFF, STATISTICSNORECOMPUTE = OFF, IGNOREDUPKEY = OFF, ALLOWROWLOCKS = ON, ALLOWPAGELOCKS = ON)
)
END
GO
/****** Object: UserDefinedFunction dbo.F Script Date: 12/22/2006 09:14:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE objectid = OBJECTID(N'dbo.F') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION dbo.F() RETURNS INT AS BEGIN
RETURN (SELECT COUNT(*) FROM T)
END
'
END
GO

Which fails with the error:
Msg 208, Level 16, State 1, Line 3
Invalid object name 'DBO.F'.

Razvan
Dec 22, 2006 at 6:59 PM
Thanks for the report and simple repro. We are investigating.

Regards,
alex
Jan 4, 2007 at 9:29 AM
Here is another case where Database Publishing Wizard 1.0 RC (0.3.0.0) fails to generate a correct script. Create a database by executing the following statements:

CREATE DATABASE TestDatabase
GO
USE TestDatabase
CREATE TABLE T (A INT PRIMARY KEY)
GO
CREATE VIEW V AS
SELECT A FROM T
GO
CREATE FUNCTION F() RETURNS INT AS BEGIN
RETURN (SELECT MIN(A) FROM V)
END
GO
ALTER TABLE T ADD B AS dbo.F()

Database Publishing Wizard generates the following script:

/****** Object: UserDefinedFunction dbo.F Script Date: 01/04/2007 12:07:46 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE objectid = OBJECTID(N'dbo.F') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.F
GO
/****** Object: Table dbo.T Script Date: 01/04/2007 12:07:46 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE objectid = OBJECTID(N'dbo.T') AND type in (N'U'))
DROP TABLE dbo.T
GO
/****** Object: View dbo.V Script Date: 01/04/2007 12:07:46 ******/
IF EXISTS (SELECT * FROM sys.views WHERE objectid = OBJECTID(N'dbo.V'))
DROP VIEW dbo.V
GO
/****** Object: View dbo.V Script Date: 01/04/2007 12:07:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE objectid = OBJECTID(N'dbo.V'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW dbo.V AS
SELECT A FROM T
'
GO
/****** Object: UserDefinedFunction dbo.F Script Date: 01/04/2007 12:07:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE objectid = OBJECTID(N'dbo.F') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION dbo.F() RETURNS INT AS BEGIN
RETURN (SELECT MIN(A) FROM V)
END
'
END
GO
/****** Object: Table dbo.T Script Date: 01/04/2007 12:07:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE objectid = OBJECTID(N'dbo.T') AND type in (N'U'))
BEGIN
CREATE TABLE dbo.T(
Aint NOT NULL,
B AS (dbo.F()),
CONSTRAINT PK__T__7C8480AE PRIMARY KEY CLUSTERED
(
A ASC
)WITH (PADINDEX = OFF, STATISTICSNORECOMPUTE = OFF, IGNOREDUPKEY = OFF, ALLOWROWLOCKS = ON, ALLOWPAGELOCKS = ON)
)
END
GO

Which fails with the error:
Msg 208, Level 16, State 1, Procedure V, Line 2
Invalid object name 'T'.

Incidentally, if we generate the script using the "Drop existing objects in script" option set to False, the generated script fails with the same error on the first run, but completes with no error if it is run a second time. But this is not a decent workaround, it's just an accident.

You may argue that here we have a circular dependency and there is no way that the Wizard can correctly generate a script in such a case (without adding columns later, the way it's done in the original script). However, I'd like to point out that we also have a circular dependency in this case:

CREATE DATABASE TestDatabase2
GO
USE TestDatabase2
CREATE TABLE T1 (X INT PRIMARY KEY)
GO
CREATE FUNCTION F1() RETURNS INT AS BEGIN
RETURN (SELECT COUNT(*) FROM T1)
END
GO
ALTER TABLE T1 ADD Y AS dbo.F1()

However, for this database, the script generated by Database Publishing Wizard executes successfully.

I think the way to solve such circular dependency problems is to assign different priorities to the dependencies between objects, according to the class that is found in sys.sqldependencies. If the classdesc is "OBJECTORCOLUMNREFERENCESCHEMABOUND", we need to assign a higher priority for that dependency (compared to the dependencies that have an "OBJECTORCOLUMNREFERENCENONSCHEMABOUND" classdesc).

I have yet to determine if it's possible to create a circular dependency only involving schema-bound dependencies.

Razvan
Jan 18, 2007 at 11:36 PM
Hi Razvan

Thanks for the feedback. We have fixed the issues you identified in 1.0 release.

Thanks
Himanshu