Problem with CTE

informatika

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

Brian Donahue

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.

informatika

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

Brian Donahue

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

informatika

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

informatika

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

Brian Donahue

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.

Eddie D

This issue is fixed in SQL Compare V.7.0

Eddie Davis