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
  1. Mötz Jensen left a comment on February 26, 2012 at 4:39 pm

    Hi.

    I also hit the FASTFIRSTROW keyword issue on a AX 2012 installation with a SQL server 2012 RC0.

    How did you guys perform the installation? Did you restore a contoso backup onto the SQL server?

    I got the FASTFIRSTROW error while trying to install a clean database from the AX 2012 installer. I managed to restore a backup from a SQL server 2008 R2 installation into the SQL 2012 RC0 and start my AOS server. Compatibility mode was set to 100.

    I found several places when running the AX 2012 client where the FASTFIRSTROW error did break my session.

  2. Jeroen Doens left a comment on February 27, 2012 at 7:34 pm

    We restored an Ax2012 database that was installed on a SQL Server 2008 R2 database. Then we modified the Ax stored procedures through the Management Studio, so they don’t use the FASTFIRSTROW keyword any more.

Comments are closed.