Install Ax2012 on SQL Server 2012 RC0

February 13, 2012 2 comments

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