Red Gate forums :: View topic - Dependecy Problem
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager Previous Versions
SQL Packager Previous Versions forum

Dependecy Problem

Search in SQL Packager Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
mike.hebert



Joined: 01 Aug 2007
Posts: 4
Location: Edmonton, Alberta, Canada

PostPosted: Wed Nov 28, 2007 7:45 pm    Post subject: Dependecy Problem Reply with quote

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!

Code:
/****** 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:

Code:
/****** 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'
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6641

PostPosted: Thu Nov 29, 2007 10:24 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group