UPD: ILM "2" RC Installation Failure (SQLStrings, SQL2008)
Hello! I'm trying to install ILM"2" RCon new WS2008 system, remote SQL2005 SP2 and have got an error bellow. Any ideas? Thanks in advance! ================================================================================= From evtlog: Product: Microsoft Identity Lifecycle Manager "2" -- Error 26204. Error -2147217900: failed to execute SQL string, error detail: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon., SQL key: RavenDB.WindowsAuthentication.ObjectsTables SQL string: CREATE NONCLUSTERED INDEX IX_ObjectsInternal_AttributeKeyValueInteger ON dbo.ObjectsInternal ( AttributeKey ASC, ValueInteger ASC, ObjectTypeKey ASC ) INCLUDE(AddTime, DeleteTime) WHERE ValueInteger is not null WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] ================================================================================= From verbose msiexeclog: MSI (s) (AC:AC) [16:59:10:636]: Note: 1: 2360 MSI (s) (AC:AC) [16:59:10:636]: Executing op: FileCopy(SourceName=pwd.wsp|ILMPasswordPortal.wsp,SourceCabKey=ILMPasswordPortal.wsp.62B8CEEF_1020_4520_8B7C_A5A4C498EB66,DestName=ILMPasswordPortal.wsp,Attributes=512,FileSize=26624,PerTick=32768,,VerifyMedia=1,,,,,CheckCRC=0,,,InstallMode=58982400,HashOptions=0,HashPart1=1843471803,HashPart2=295808826,HashPart3=1081642548,HashPart4=-947188434,,)MSI (s) (AC:AC) [16:59:10:636]: File: C:\Program Files\Microsoft Identity Management\Web Portals\ILMPasswordPortal.wsp;To be installed;Won't patch;No existing fileMSI (s) (AC:AC) [16:59:10:636]: Source for file 'ILMPasswordPortal.wsp.62B8CEEF_1020_4520_8B7C_A5A4C498EB66' is compressedMSI (s) (AC:AC) [16:59:10:636]: Note: 1: 2318 2: C:\Program Files\Microsoft Identity Management\Web Portals\ILMPasswordPortal.wsp MSI (s) (AC:AC) [16:59:10:636]: Executing op: SetTargetFolder(Folder=C:\Windows\system32\)MSI (s) (AC:AC) [16:59:10:636]: Executing op: SetSourceFolder(Folder=1\Windows\system32\)MSI (s) (AC:AC) [16:59:10:636]: Executing op: FileCopy(SourceName=sqmapi.dll|Microsoft.IdentityManagement.Sqm.dll,SourceCabKey=Microsoft.IdentityManagement.Sqm.dll,DestName=Microsoft.IdentityManagement.Sqm.dll,Attributes=512,FileSize=39800,PerTick=32768,,VerifyMedia=1,,,,,CheckCRC=0,Version=4.0.2173.0,Language=0,InstallMode=58982400,,,,,,,)MSI (s) (AC:AC) [16:59:10:636]: File: C:\Windows\system32\Microsoft.IdentityManagement.Sqm.dll;To be installed;Won't patch;No existing fileMSI (s) (AC:AC) [16:59:10:636]: Source for file 'Microsoft.IdentityManagement.Sqm.dll' is compressedMSI (s) (AC:AC) [16:59:10:636]: Note: 1: 2318 2: C:\Windows\system32\Microsoft.IdentityManagement.Sqm.dll MSI (s) (AC:AC) [16:59:10:652]: Executing op: CacheSizeFlush(,)MSI (s) (AC:AC) [16:59:10:652]: Executing op: ActionStart(Name=RollbackCreateDatabase,,)MSI (s) (AC:AC) [16:59:10:652]: Executing op: CustomActionSchedule(Action=RollbackCreateDatabase,ActionType=1281,Source=BinaryData,Target=DropDatabase,CustomActionData=RavenDB.WindowsAuthenticationRUMSKdb08MSILM11)MSI (s) (AC:AC) [16:59:10:652]: Executing op: ActionStart(Name=CreateDatabase,Description=Creating Databases,)MSI (s) (AC:AC) [16:59:10:667]: Executing op: CustomActionSchedule(Action=CreateDatabase,ActionType=1025,Source=BinaryData,Target=CreateDatabase,CustomActionData=RavenDB.WindowsAuthenticationRUMSKdb08MSILM1100)MSI (s) (AC:40) [16:59:10:667]: Invoking remote custom action. DLL: C:\Windows\Installer\MSI5AAB.tmp, Entrypoint: CreateDatabaseMSI (s) (AC:AC) [16:59:11:339]: Executing op: ActionStart(Name=ExecuteSqlStrings,Description=Executing SQL Strings,)MSI (s) (AC:AC) [16:59:11:433]: Executing op: CustomActionSchedule(Action=ExecuteSqlStrings,ActionType=1025,Source=BinaryData,Target=ExecuteSqlStrings,CustomActionData=RavenDB.WindowsAuthenticationRUMSKdb08MSILM11RavenDB.WindowsAuthentication.SqlErrorMessages1IF(EXISTS( SELECT m.message_id FROM sys.messages AS m WHERE m.message_id = 60000))BEGIN EXECUTE sp_dropmessage 60000;END EXECUTE sp_addmessage 60000,10,N'Reraised Error %d, Level %d, State %d, Procedure %s, Line %d, Message: %s';/** If any of the exception messages in this file are modified, the corresponding signatures in the DataAccess.cs module should be * changed to match. */IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[FormatParameterName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION dbo.[FormatParameterName]RavenDB.WindowsAuthentication.SqlErrorMessages1CREATE FUNCTION [dbo].[FormatParameterName] (@parameterName NVARCHAR(448))RETURNS NVARCHAR(448)ASBEGINDECLARE @result NVARCHAR(448);MSI (s) (AC4) [16:59:11:433]: Invoking remote custom action. DLL: C:\Windows\Installer\MSI5DAA.tmp, Entrypoint: ExecuteSqlStringsExecuteSqlStrings: Error 0x80040e14: failed to execute SQL string, error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon., SQL key: RavenDB.WindowsAuthentication.ObjectsTables SQL string: CREATE NONCLUSTERED INDEX IX_ObjectsInternal_AttributeKeyValueInteger ON dbo.ObjectsInternal ( AttributeKey ASC, ValueInteger ASC, ObjectTypeKey ASC)INCLUDE(AddTime, DeleteTime)WHERE ValueInteger is not nullWITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON MSI (s) (AC!A8) [16:59:17:964]: Product: Microsoft Identity Lifecycle Manager "2" -- Error 26204. Error -2147217900: failed to execute SQL string, error detail: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon., SQL key: RavenDB.WindowsAuthentication.ObjectsTables SQL string: CREATE NONCLUSTERED INDEX IX_ObjectsInternal_AttributeKeyValueInteger ON dbo.ObjectsInternal ( AttributeKey ASC, ValueInteger ASC, ObjectTypeKey ASC)INCLUDE(AddTime, DeleteTime)WHERE ValueInteger is not nullWITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] Action ended 16:59:17: InstallFinalize. Return value 3. ================================================================================= From SQL Trace: SQL:BatchCompletedCREATE TABLE dbo.ObjectsInternal( ObjectID UNIQUEIDENTIFIER NOT NULL, ObjectTypeKey SMALLINT NOT NULL, AttributeKey SMALLINT NOT NULL, ValueBinary VARBINARY(max) NULL, ValueBoolean BIT NULL, ValueDateTime DATETIME NULL, ValueInteger BIGINT NULL, ValueReference UNIQUEIDENTIFIER NULL, ValueString NVARCHAR(448) NULL, ValueText NVARCHAR(max) NULL, AddTime DATETIME NOT NULL, AddCause UNIQUEIDENTIFIER NOT NULL, DeleteTime DATETIME NOT NULL DEFAULT '9999', DeleteCause UNIQUEIDENTIFIER NULL, LocaleKey SMALLINT NULL) ON [PRIMARY]Windows Installer - UnicodeadminRU\admin023771033081182008-11-14 16:30:47.7232008-11-14 16:30:47.740SQL:BatchStartingCREATE CLUSTERED INDEX IX_ObjectsInternal_ObjectID ON dbo.ObjectsInternal ( ObjectID ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]Windows Installer - UnicodeadminRU\admin33081182008-11-14 16:30:47.740SQL:BatchCompletedCREATE CLUSTERED INDEX IX_ObjectsInternal_ObjectID ON dbo.ObjectsInternal ( ObjectID ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]Windows Installer - UnicodeadminRU\admin01681233081182008-11-14 16:30:47.7402008-11-14 16:30:47.740SQL:BatchStartingCREATE NONCLUSTERED INDEX [IX_ObjectsInternal_AddTimeDateTime] ON [dbo].[ObjectsInternal] ([AddTime] ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]Windows Installer - UnicodeadminRU\admin33081182008-11-14 16:30:47.740SQL:BatchCompletedCREATE NONCLUSTERED INDEX [IX_ObjectsInternal_AddTimeDateTime] ON [dbo].[ObjectsInternal] ([AddTime] ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]Windows Installer - UnicodeadminRU\admin0440133081182008-11-14 16:30:47.7402008-11-14 16:30:47.740SQL:BatchStartingCREATE NONCLUSTERED INDEX [IX_ObjectsInternal_DeleteTimeDateTime] ON [dbo].[ObjectsInternal] ([DeleteTime] ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]Windows Installer - UnicodeadminRU\admin33081182008-11-14 16:30:47.757SQL:BatchCompletedCREATE NONCLUSTERED INDEX [IX_ObjectsInternal_DeleteTimeDateTime] ON [dbo].[ObjectsInternal] ([DeleteTime] ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]Windows Installer - UnicodeadminRU\admin0440133081182008-11-14 16:30:47.7572008-11-14 16:30:47.757SQL:BatchStartingCREATE NONCLUSTERED INDEX IX_ObjectsInternal_AttributeKeyValueInteger ON dbo.ObjectsInternal ( AttributeKey ASC, ValueInteger ASC, ObjectTypeKey ASC)INCLUDE(AddTime, DeleteTime)WHERE ValueInteger is not nullWITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]Windows Installer - UnicodeadminRU\admin33081182008-11-14 16:30:47.757SQL:BatchCompletedCREATE NONCLUSTERED INDEX IX_ObjectsInternal_AttributeKeyValueInteger ON dbo.ObjectsInternal ( AttributeKey ASC, ValueInteger ASC, ObjectTypeKey ASC)INCLUDE(AddTime, DeleteTime)WHERE ValueInteger is not nullWITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]Windows Installer - UnicodeadminRU\admin000033081182008-11-14 16:30:47.7572008-11-14 16:30:47.757
November 14th, 2008 5:34pm

Starting with the RC and moving forward, ILM V2 requires SQL 2008.
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2008 6:06pm

Sorry, I'm dumb : ) Though one might expect pretty basic prerequisites check from RC build : )
November 14th, 2008 6:10pm

Well, maybe I'm dumb, but I have the same error with local SQL 2008 server this time... Any ideas? Thanks in advance. From verbose installation log: Code Snippet ========================================= MSI (s) (BC:D8) [15:37:30:603]: Executing op: FileCopy(SourceName=pwd.wsp|ILMPasswordPortal.wsp,SourceCabKey=ILMPasswordPortal.wsp.62B8CEEF_1020_4520_8B7C_A5A4C498EB66,DestName=ILMPasswordPortal.wsp,Attributes=512,FileSize=26624,PerTick=32768,,VerifyMedia=1,,,,,CheckCRC=0,,,InstallMode=58982400,HashOptions=0,HashPart1=1843471803,HashPart2=295808826,HashPart3=1081642548,HashPart4=-947188434,,)MSI (s) (BC:D8) [15:37:30:603]: File: C:\Program Files\Microsoft Identity Management\Web Portals\ILMPasswordPortal.wsp;To be installed;Won't patch;No existing fileMSI (s) (BC:D8) [15:37:30:603]: Source for file 'ILMPasswordPortal.wsp.62B8CEEF_1020_4520_8B7C_A5A4C498EB66' is compressedMSI (s) (BC:D8) [15:37:30:604]: Note: 1: 2318 2: C:\Program Files\Microsoft Identity Management\Web Portals\ILMPasswordPortal.wsp MSI (s) (BC:D8) [15:37:30:606]: Executing op: SetTargetFolder(Folder=C:\Windows\system32\)MSI (s) (BC:D8) [15:37:30:607]: Executing op: SetSourceFolder(Folder=1\Windows\system32\)MSI (s) (BC:D8) [15:37:30:607]: Executing op: FileCopy(SourceName=sqmapi.dll|Microsoft.IdentityManagement.Sqm.dll,SourceCabKey=Microsoft.IdentityManagement.Sqm.dll,DestName=Microsoft.IdentityManagement.Sqm.dll,Attributes=512,FileSize=39800,PerTick=32768,,VerifyMedia=1,,,,,CheckCRC=0,Version=4.0.2173.0,Language=0,InstallMode=58982400,,,,,,,)MSI (s) (BC:D8) [15:37:30:607]: File: C:\Windows\system32\Microsoft.IdentityManagement.Sqm.dll;To be installed;Won't patch;No existing fileMSI (s) (BC:D8) [15:37:30:608]: Source for file 'Microsoft.IdentityManagement.Sqm.dll' is compressedMSI (s) (BC:D8) [15:37:30:608]: Note: 1: 2318 2: C:\Windows\system32\Microsoft.IdentityManagement.Sqm.dll MSI (s) (BC:D8) [15:37:30:611]: Executing op: CacheSizeFlush(,)MSI (s) (BC:D8) [15:37:30:611]: Executing op: ActionStart(Name=RollbackCreateDatabase,,)MSI (s) (BC:D8) [15:37:30:622]: Executing op: CustomActionSchedule(Action=RollbackCreateDatabase,ActionType=1281,Source=BinaryData,Target=DropDatabase,CustomActionData=RavenDB.WindowsAuthenticationRUMSKTSTILM01MSILM11)MSI (s) (BC:D8) [15:37:30:624]: Executing op: ActionStart(Name=CreateDatabase,Description=Creating Databases,)MSI (s) (BC:D8) [15:37:30:626]: Executing op: CustomActionSchedule(Action=CreateDatabase,ActionType=1025,Source=BinaryData,Target=CreateDatabase,CustomActionData=RavenDB.WindowsAuthenticationRUMSKTSTILM01MSILM1100)MSI (s) (BC:F8) [15:37:30:631]: Invoking remote custom action. DLL: C:\Windows\Installer\MSI68D8.tmp, Entrypoint: CreateDatabaseMSI (s) (BC:D8) [15:37:31:239]: Executing op: ActionStart(Name=ExecuteSqlStrings,Description=Executing SQL Strings,)MSI (s) (BC:D8) [15:37:31:404]: Executing op: CustomActionSchedule(Action=ExecuteSqlStrings,ActionType=1025,Source=BinaryData,Target=ExecuteSqlStrings,CustomActionData=RavenDB.WindowsAuthenticationRUMSKTSTILM01MSILM11RavenDB.WindowsAuthentication.SqlErrorMessages1IF(EXISTS( SELECT m.message_id FROM sys.messages AS m WHERE m.message_id = 60000))BEGIN EXECUTE sp_dropmessage 60000;END EXECUTE sp_addmessage 60000,10,N'Reraised Error %d, Level %d, State %d, Procedure %s, Line %d, Message: %s';/** If any of the exception messages in this file are modified, the corresponding signatures in the DataAccess.cs module should be * changed to match. */IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[FormatParameterName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION dbo.[FormatParameterName]RavenDB.WindowsAuthentication.SqlErrorMessages1CREATE FUNCTION [dbo].[FormatParameterName] (@parameterName NVARCHAR(448))RETURNS NVARCHAR(448)ASBEGINDECLARE @result NVARCHAR(4MSI (s) (BC:C8) [15:37:31:417]: Invoking remote custom action. DLL: C:\Windows\Installer\MSI6BF6.tmp, Entrypoint: ExecuteSqlStringsExecuteSqlStrings: Error 0x80040e14: failed to execute SQL string, error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon., SQL key: RavenDB.WindowsAuthentication.ObjectsTables SQL string: CREATE NONCLUSTERED INDEX IX_ObjectsInternal_AttributeKeyValueInteger ON dbo.ObjectsInternal ( AttributeKey ASC, ValueInteger ASC, ObjectTypeKey ASC)INCLUDE(AddTime, DeleteTime)WHERE ValueInteger is not nullWITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON MSI (s) (BC!24) [15:37:46:137]: Product: Microsoft Identity Lifecycle Manager "2" -- Error 26204. Error -2147217900: failed to execute SQL string, error detail: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon., SQL key: RavenDB.WindowsAuthentication.ObjectsTables SQL string: CREATE NONCLUSTERED INDEX IX_ObjectsInternal_AttributeKeyValueInteger ON dbo.ObjectsInternal ( AttributeKey ASC, ValueInteger ASC, ObjectTypeKey ASC)INCLUDE(AddTime, DeleteTime)WHERE ValueInteger is not nullWITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] Action ended 15:37:46: InstallFinalize. Return value 3.
Free Windows Admin Tool Kit Click here and download it now
November 18th, 2008 3:46pm

ILM 2 is not compatible with SQL 2005. You need to install SQL 2008! Cheers, Markus ///////////////////////////////////////////////////////////////////////Markus VilcinskasTechnical WriterMicrosoft Identity Integration Servermailto:markvi@microsoft.com.NO_SPAMThis posting is provided "AS IS" with no warranties, and confers no rights.Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/copyright.htm///////////////////////////////////////////////////////////////////////
November 18th, 2008 9:11pm

Markus, thanks for reply, but I have the error with SQL2008 as I wrote above.
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2008 9:59am

Please check the Connect newgroup for ILM "2" issues: https://connect.microsoft.com/community/discussion/richui/default.aspx?SiteID=433
November 25th, 2008 12:56am

Ahmad, thanks. I've allready checkedthe forum and even replied to a thread with similiar sympthoms ("Error -2147217900 Error on ILM2 Install" Posted by Pete Holmes on 11/10/2008). But I stilll fail to find any resolution there. Thank you.
Free Windows Admin Tool Kit Click here and download it now
November 25th, 2008 10:00am

I have seen the discussion generated there, and we all agreed it looked like SQL Server 2005 was installed.
December 2nd, 2008 8:39pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics