Wrong order in synchronization script

Hello,
with new SQL Server 2005 it's not possible to update our databases in one step anymore, because SQL Compare creates some functions in wrong order.
I have made a script for you to test. It creates two databases: TestA and TestB. After successful running the script, you can try to synchronize from TestA to TestB. In my environment it doesn't work. Hope you get the same result:
We use SQL Server 2005, SP1 and SQL Compare 5.2.0.32
Thanks for any help,
;-), Mike
with new SQL Server 2005 it's not possible to update our databases in one step anymore, because SQL Compare creates some functions in wrong order.
I have made a script for you to test. It creates two databases: TestA and TestB. After successful running the script, you can try to synchronize from TestA to TestB. In my environment it doesn't work. Hope you get the same result:
USE Master GO IF EXISTS( SELECT * FROM sys.databases WHERE Name = 'TestA' ) BEGIN DROP DATABASE TestA END CREATE DATABASE TestA IF EXISTS( SELECT * FROM sys.databases WHERE Name = 'TestB' ) BEGIN DROP DATABASE TestB END CREATE DATABASE TestB GO USE TestA GO CREATE TABLE [dbo].[Kostenstelle] ( [Kostenstelle_ID] [int] IDENTITY(1,1) NOT NULL, [Sort] [float] NOT NULL CONSTRAINT [DF_Kostenstelle_Sort] DEFAULT (0), [Gehoert_Zu_Kostenstelle_ID] [int] NULL, [BaumSortierung] [int] NULL, [Ebene] [int] NULL, CONSTRAINT [PK_Kostenstelle] PRIMARY KEY CLUSTERED ( [Kostenstelle_ID] ASC ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE FUNCTION [dbo].[KsBaum] ( @Start_Kostenstelle_ID INT, @Anwender_ID INT = NULL, @Berechtigung INT = 0 ) RETURNS TABLE AS RETURN ( WITH KostenstelleBaum( Kostenstelle_ID ) AS ( SELECT Kostenstelle_ID FROM Kostenstelle WHERE Kostenstelle_ID = @Start_Kostenstelle_ID OR ( Gehoert_Zu_Kostenstelle_ID IS NULL AND @Start_Kostenstelle_ID IS NULL ) OR Gehoert_Zu_Kostenstelle_ID = Kostenstelle_ID UNION ALL SELECT Kostenstelle.Kostenstelle_ID FROM Kostenstelle INNER JOIN KostenstelleBaum ON KostenstelleBaum.Kostenstelle_ID = Kostenstelle.Gehoert_Zu_Kostenstelle_ID WHERE Kostenstelle.Kostenstelle_ID <> Kostenstelle.Gehoert_Zu_Kostenstelle_ID ) SELECT Kostenstelle.* FROM Kostenstelle INNER JOIN KostenstelleBaum ON KostenstelleBaum.Kostenstelle_ID = Kostenstelle.Kostenstelle_ID ) GO CREATE FUNCTION [dbo].[KsBerechtigung] ( @Anwender_ID INT, @Berechtigung INT ) RETURNS TABLE AS RETURN ( WITH KostenstelleBerechtigung( Kostenstelle_ID ) AS ( SELECT Kostenstelle.Kostenstelle_ID FROM Kostenstelle -- LEFT JOIN ZU_Anwender_Kostenstelle -- ON Kostenstelle.Kostenstelle_ID = ZU_Anwender_Kostenstelle.Kostenstelle_ID WHERE @Berechtigung <= 20 -- OR ZU_Anwender_Kostenstelle.Anwender_ID = @Anwender_ID UNION ALL SELECT Kostenstelle.Kostenstelle_ID FROM Kostenstelle INNER JOIN KostenstelleBerechtigung ON Kostenstelle.Gehoert_Zu_Kostenstelle_ID = KostenstelleBerechtigung.Kostenstelle_ID WHERE @Berechtigung > 20 ) SELECT DISTINCT Kostenstelle_ID FROM KostenstelleBerechtigung ) GO ALTER FUNCTION [dbo].[KsBaum] ( @Start_Kostenstelle_ID INT, @Anwender_ID INT = NULL, @Berechtigung INT = 0 ) RETURNS TABLE AS RETURN ( WITH KostenstelleBaum( Kostenstelle_ID ) AS ( SELECT Kostenstelle_ID FROM Kostenstelle WHERE Kostenstelle_ID = @Start_Kostenstelle_ID OR ( Gehoert_Zu_Kostenstelle_ID IS NULL AND @Start_Kostenstelle_ID IS NULL ) OR Gehoert_Zu_Kostenstelle_ID = Kostenstelle_ID UNION ALL SELECT Kostenstelle.Kostenstelle_ID FROM Kostenstelle INNER JOIN KostenstelleBaum ON KostenstelleBaum.Kostenstelle_ID = Kostenstelle.Gehoert_Zu_Kostenstelle_ID WHERE Kostenstelle.Kostenstelle_ID <> Kostenstelle.Gehoert_Zu_Kostenstelle_ID ) SELECT Kostenstelle.* FROM Kostenstelle INNER JOIN KostenstelleBaum ON KostenstelleBaum.Kostenstelle_ID = Kostenstelle.Kostenstelle_ID INNER JOIN dbo.KsBerechtigung( @Anwender_ID, @Berechtigung ) KostenstelleBerechtigung ON KostenstelleBerechtigung.Kostenstelle_ID = Kostenstelle.Kostenstelle_ID )
We use SQL Server 2005, SP1 and SQL Compare 5.2.0.32
Thanks for any help,
;-), Mike
Comments
Thanks for your patience and the test script. I can confirm that the we get the issue too. However, I am not sure at this stage when a fix will be ready, I will have a word with the development team and will get back to you in the next few days.
Regards,
Jonathan
-Project Manager
-Red Gate Software Ltd
Eddie Davis
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com