During the preparations for Microsoft TechDays 2012 my colleague and I installed a fresh Windows Server 2008 R2 with SQL Server 2012 and Ax 2012. At first all seems to work (the AOS started and we could login with the client), but when we started to prepare and opened the ‘Datasource Name’ lookup on the ‘Document Datasources’ we got an error. After some research my colleague Kenny found that the SQL UtilElements-view used a deprecated keyword called FASTFIRSTROW. After updating the view this functionality seems to work fine.
The alter of the view should look like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | USE [MicrosoftDynamicsAX] GO /****** Object: View [dbo].[UTILELEMENTS] Script Date: 13/02/2012 10:12:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[UTILELEMENTS] AS SELECT -- Select all Root Elements or Id based children or Root Elements with no children d.[LayerId] AS UTILLEVEL, e.[ElementType] AS RECORDTYPE, e.[ParentId] AS PARENTID, e.[Name] AS NAME, cs.[SourceText] AS SOURCE, CAST(NULL AS varbinary(MAX)) AS CODE, d.[BASEVERSION], d.[VERSION], d.[SAVECOUNT], d.[MODIFIEDDATETIME], 0 AS DEL_MODIFIEDTIME, d.[MODIFIEDBY], d.[CREATEDDATETIME], 0 AS DEL_CREATEDTIME, d.[CREATEDBY], d.ElementVersion AS RECVERSION, d.[LayerId] * CAST(268435456 AS BIGINT) + e.[ElementHandle] AS RECID FROM [dbo].[ModelElement] AS e WITH (READUNCOMMITTED) INNER JOIN [dbo].[ModelElementData] AS d WITH (READUNCOMMITTED) ON d.ElementHandle = e.ElementHandle LEFT OUTER JOIN [dbo].[Sources] AS cs WITH (READUNCOMMITTED) ON cs.SourceHandle = e.ElementHandle AND cs.LayerId = d.LayerId WHERE e.ParentHandle = 0 -- Only Root element AND NOT EXISTS (SELECT 1 FROM [dbo].[ModelElement] AS gc WITH (READUNCOMMITTED) WHERE gc.ParentHandle = e.ElementHandle AND gc.ParentId = 0) OR (e.ParentHandle <> 0 AND e.ParentId <> 0) -- Or Not granular child UNION ALL SELECT -- Select all Granular Root Elements children ug.[LayerId] AS UTILLEVEL, -- dgc = Distinct Granular Child Layers e.[ElementType] AS RECORDTYPE, e.[ParentId] AS PARENTID, e.[Name] AS NAME, cs.[SourceText] AS SOURCE, CAST(NULL AS varbinary(MAX)) AS CODE, d.[BASEVERSION], d.[VERSION], d.[SAVECOUNT], d.[MODIFIEDDATETIME], 0 AS DEL_MODIFIEDTIME, d.[MODIFIEDBY], d.[CREATEDDATETIME], 0 AS DEL_CREATEDTIME, d.[CREATEDBY], d.ElementVersion AS RECVERSION, d.[LayerId] * CAST(268435456 AS BIGINT) + e.[ElementHandle] AS RECID FROM [dbo].[ModelElement] AS e WITH (READUNCOMMITTED) INNER JOIN [dbo].[UtilGranularLayers] AS ug WITH (READUNCOMMITTED) ON ug.RootHandle = e.ElementHandle INNER JOIN [dbo].[ModelElementData] AS d WITH (READUNCOMMITTED) ON d.ElementHandle = e.ElementHandle LEFT OUTER JOIN [dbo].[Sources] AS cs WITH (READUNCOMMITTED) ON cs.SourceHandle = e.ElementHandle AND cs.LayerId = d.LayerId WHERE e.ParentHandle = 0 -- Only Root element AND EXISTS (SELECT 1 FROM [dbo].ModelElement AS gc WITH (READUNCOMMITTED) WHERE gc.ParentHandle = e.ElementHandle AND gc.ParentId = 0) -- Granular child GO |
