Dependecy Problem

mike.hebert

I am having a problem in version 5.4.0.89 where I have a function that refers to a table, and the script that Packager generates creates the function before table. It is a recursive table (i.e., there is a self-referencing foreign key) and I am using SQL 2005 Common Table Expressions to get a given job and all of its descendants.

If I save the script and manually edit it so that the function is created after the table, it works fine. I have a feeling that it is not detecting the dependency because the table is referenced from within the common table expression.

From browsing the forums, it sounds like you are going to want to see some an example, so here is the script to create the table and the function.

Note that the "audit" functions are unrelated to the problem (you can remove those columns from the definition if you actually want to create the table), and the CsvToInt function does what its name implies.

Any ideas?

Thanks!

/****** Object:  Table [dbo].[Job]    Script Date: 11/28/2007 11:37:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Job](
	[JobID] [int] IDENTITY(1,1) NOT NULL,
	[ParentJobID] [int] NULL,
	[Job] [nvarchar](50) NOT NULL,
	[Audit_CreatedBy] [varchar](128) NOT NULL CONSTRAINT [DF_Job_Audit_CreatedBy]  DEFAULT ([Audit].[GetLogin]()),
	[Audit_CreatedOnUtc] [datetime] NOT NULL CONSTRAINT [DF_Job_Audit_CreatedOnUtc]  DEFAULT (getutcdate()),
	[Audit_UpdatedBy] [varchar](128) NOT NULL CONSTRAINT [DF_Job_Audit_UpdatedBy]  DEFAULT ([Audit].[GetLogin]()),
	[Audit_UpdatedOnUtc] [datetime] NOT NULL CONSTRAINT [DF_Job_Audit_UpdatedOnUtc]  DEFAULT (getutcdate()),
 CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED 
(
	[JobID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The parent Job of this Job. A NULL value indicates that this is a root-level job.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Job', @level2type=N'COLUMN',@level2name=N'ParentJobID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the Job.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Job', @level2type=N'COLUMN',@level2name=N'Job'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The login of the user that created this record.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Job', @level2type=N'COLUMN',@level2name=N'Audit_CreatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The UTC date/time that this record was created.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Job', @level2type=N'COLUMN',@level2name=N'Audit_CreatedOnUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The login of the user that last updated this record.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Job', @level2type=N'COLUMN',@level2name=N'Audit_UpdatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The UTC date/time that this record was last updated.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Job', @level2type=N'COLUMN',@level2name=N'Audit_UpdatedOnUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Jobs represent various Jobs and Jobsites for the organization. An Asset can be assigned to a Job, and each Job may belong to another Job (that is, be a "Sub-Job").' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Job'
GO
ALTER TABLE [dbo].[Job]  WITH CHECK ADD  CONSTRAINT [FK_Job_Job] FOREIGN KEY([ParentJobID])
REFERENCES [dbo].[Job] ([JobID])
GO
ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_Job]

And here is the function:

/****** Object:  UserDefinedFunction [dbo].[GetJobWithDescendants]    Script Date: 11/28/2007 11:37:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Mike Hebert
-- Create date: June 15, 2007
-- Description:	Gets the specified Jobs and all
--	of their descendants.
-- =============================================
CREATE FUNCTION [dbo].[GetJobWithDescendants]
(
	@JobIDCsv	VARCHAR(MAX)
)
RETURNS TABLE 
AS
RETURN 
(
	WITH JobTree(JobId, ParentJobId, Job)
	AS 
	(
		SELECT A.JobId, A.ParentJobId, Job
		FROM dbo.Job A
		WHERE A.JobId IN (SELECT IntValue FROM dbo.CsvToInt(@JobIDCsv))
	UNION ALL
		SELECT R.JobId, R.ParentJobId, R.Job
		FROM dbo.Job R
		INNER JOIN JobTree T ON R.ParentJobId = T.JobId
	)
	SELECT DISTINCT JobID, ParentJobID, Job
	FROM JobTree
)


GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Gets the specified Jobs and all of their descendants.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'GetJobWithDescendants'

Brian Donahue

Hi Mike,

It sounds like you have encountered the comparison engine bug described here. For now, I can't think of a better workaround than to save the SQL script and move the CTE's base table DDL so that it runs before the function.