Problem with CTE

informatikainformatika Posts: 18
edited July 24, 2008 11:56AM in SQL Compare Previous Versions
When I synchronize a new, empty Database I get following error, because of a wrong executing order and I think, it's because of the CTE:

The following error message was returned from the SQL Server:

[208] Ungültiger Objektname 'MetadatumTyp'.

The following SQL command caused the error:


CREATE FUNCTION MetadatumTypSort
(
@Root_MetadatumTyp_ID INT
)
RETURNS TABLE
AS

RETURN
(
WITH MetadatumTypBaum( MetadatumTyp_ID, n, e )
AS
(
SELECT MetadatumTyp_ID, CAST( STR( ROW_NUMBER() OVER ( ORDER BY Sort ), 3 ) AS NVARCHAR( 66 ) ) AS n, 0
FROM MetadatumTyp
WHERE Gehoert_Zu_MetadatumTyp_ID = @Root_MetadatumTyp_ID
OR ( Gehoert_Zu_MetadatumTyp_ID IS NULL AND @Root_MetadatumTyp_ID IS NULL )
OR Gehoert_Zu_MetadatumTyp_ID = MetadatumTyp_ID

UNION ALL

SELECT MetadatumTyp.MetadatumTyp_ID, CAST( MetadatumTypBaum.n + STR( ROW_NUMBER() OVER ( ORDER BY Sort ), 3 ) AS NVARCHAR( 66 ) ) AS n, e = e + 1
FROM MetadatumTyp
INNER JOIN MetadatumTypBaum
ON MetadatumTypBaum.MetadatumTyp_ID = MetadatumTyp.Gehoert_Zu_MetadatumTyp_ID
WHERE MetadatumTyp.MetadatumTyp_ID <> MetadatumTyp.Gehoert_Zu_MetadatumTyp_ID
AND MetadatumTypBaum.MetadatumTyp_ID <> 0
)
SELECT ROW_NUMBER() OVER ( ORDER BY n ) AS Sort, MetadatumTypBaum.e AS Ebene, MetadatumTypBaum.MetadatumTyp_ID
FROM MetadatumTypBaum
)

The Function is created, before table exists.
Synchronisation will be okay, when I first synchronise everything else and then in a second step synchronize the function. Is this a bug? Any suggestions? Any workarounds?

Thank you for helping,

Michael Kriegner, ;-), Mike
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Mike,

    You may want to verify that you are using 'include dependencies' in the options, otherwise objects can be scripted out-of-order. If the order is actually correct, you may try disabling the 'transactional plumbing'. There is some problem that I haven't got to the bottom of, where the transactions affect the synchronization even though the objects are being scripted in the correct order.
    · Share on Twitter
  • informatikainformatika Posts: 18
    Hello Brian,

    thanks for reply. I use 'include dependencies' that's not the problem.

    Following your answer, I tried to disable transactions, but the problem remains. I looked into the skript. It's not because of transactions. The functions are really in the wrong order ...

    Here are the parts of the skript:

    The function KsBaum needs the Function KsBerechtigung. But KsBerechtigung is created at the end of the skript.

    .....

    GO
    @TRANCOUNT&gt;0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Creating [dbo].[KsBaum]'
    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
    INNER JOIN dbo.KsBerechtigung( @Anwender_ID, @Berechtigung ) KostenstelleBerechtigung
    ON KostenstelleBerechtigung.Kostenstelle_ID = Kostenstelle.Kostenstelle_ID
    )


    GO
    @TRANCOUNT&gt;0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Creating [dbo].[KsSort]'
    GO

    .....

    @TRANCOUNT&gt;0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Creating [dbo].[KsBerechtigung]'
    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 ZU_Anwender_Kostenstelle.Anwender_ID = @Anwender_ID
    OR @Berechtigung <= 20

    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
    @TRANCOUNT&gt;0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO

    By the way, in SQL Compare 5.0 I have the same problem. The only hint I have are the CTEs. Everything else is working fine. I had never problems.

    Any idea?

    Thank you for helping,

    ;-), Mike
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Can you please try 4.3? It's a fully tested version of SQL Bundle that had never officially been released. Maybe this will script the objects in the correct order.

    ftp://ftp.red-gate.com/sqlbundle430.exe
    · Share on Twitter
  • informatikainformatika Posts: 18
    Sorry, 4.3 version is also not working (same like the previous version and same like verion 5).

    Any other idea? How do you find out such dependencies? By the way, we haven't installed the Service Pack 1 for SQL Server 2005 yet, could this be a problem?

    I will tell you, when we have installed SP1 ...

    Thanks,

    ;-), Mike
    · Share on Twitter
  • informatikainformatika Posts: 18
    No, SP1 doesn't help.

    Now I put my problem into SQL Compare 5. I also have an example for you, to try out.

    Thanks,

    ;-), Mike
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Mike,

    I haven't got a clue. I'm going to need to ask for your schema (snapshot, SQL script). Can you please send to support@red-gate.com? Thanks.
    · Share on Twitter
  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    This issue is fixed in SQL Compare V.7.0

    Eddie Davis
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.