SP 2010 user profile service app - ILM Database could not be created

This week I've been battling with the following error during a SharePoint 2010 user profile service app setup: ILM Database could not be created.

I had created some powershell scripts to set up the user profile service applications automatically on multiple farms. These scripts worked like a charm on most farms, except one.
The service application itself was correctly created, but when the script tried to start the User Profile Sync Service it failed. The service itself briefly went to a Started state, but reverted back to its Stopped state after a few minutes.

A glance in the event logs revealed a number of warnings, that were logged at the time the service started:

Warning 1

ILM SQL Configuration file could not be read: 
Error Parcing the SQL File: C:\Program Files\Microsoft Office Servers\14.0\Sql\StoredProcedures.sql  

Exception: Conversion of one or more characters from XML to target collation impossible

Warning 2

ILM Database could not be created: Error sent to Windows Event Log running : 
-- Proc Name:	GetObjectDisplayNames
-- Purpose:		to get display names for objects
-- Input:		@ObjectIDs, which we suppose to take in an array of Guids, in the format of:
--				(?B553ECA-F359-11DB-AABA-D0C055D89593? ... , �A5B106DC-F359-11DB-9C44-1EC155D89593?
-- Exception:
--				If failed to find certain object id, SQL error occurs
CREATE PROCEDURE [dbo].[GetObjectDisplayNames]
@ObjectIDs     NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Sql	NVARCHAR(MAX)
SET @Sql =
N' SELECT ValueString FROM ObjectValues ' +
N' WHERE AttributeName = N''DisplayName'' ' +
N' AND ObjectID IN ' + @ObjectIDs
EXEC sp_executesql @Sql
END
: Conversion of one or more characters from XML to target collation impossible

These warnings repeated themselves a few times and then it said: Sync service stopped succesfully. Just brilliant, ain't it?
As I couldn't find any clues online, I presumed I was the first to get this kind of exotic crap.
So in the next days I tried to fix it ... to no avail however. I tried:

• rerunning the script (who knows, maybe it was a one-time glitch)
• installing the user profile service app manually
• reinstalling sharepoint completely
• installing SP1
• formatting and reinstalling all servers in that $*@! farm

But finally I found a workaround. It involves tampering with some out-of-the-box installation files, so it will void your warranty/support from Microsoft. Proceed with caution! ;)

Solution

I noticed there was a strange token in the message displayed in the second warning in the event-log.

Could it be that easy? Was this token the source of all that "Conversion of one or more characters from XML to target collation impossible" crap?
I was pretty desperate at that time, so I was willing to give it a shot. Here's what I did:

1. Open an explorer window and go to C:\Program Files\Microsoft Office Servers\14.0\Sql
2. Locate the file StoredProcedures.sql, this contains the stored proc that was mentioned in the error.
3. Take a backup of this file as we'll have to modify it. Always take a backup if you're going to tinker with ootb SharePoint files ;)
4. Open the file in notepad and look for the entry CREATE PROCEDURE [dbo].[GetObjectDisplayNames]
5. If you scroll up a bit, you should notice a line mentioning "... , ’A5B106DC-F359-11DB-9C44-1EC155D89593?"
6. Now replace that ’ sign in front of the A5B106DC-F359-11DB-9C44-1EC155D89593 guid with a ? char.
7. Save the file again

After that I reran the script and a miracle happened (*insert praise for whatever deity you believe in*): everything worked like a charm. The sync service started properly after a few minutes and the user profile service app synced properly.

I'm still not sure why this error only occurred on one farm, while all the others installed without any hassle. All I know is that this fixed it for me. Feel free to leave your ideas on why it works in the comments :)


So there you have it, another dirty fix for a SharePoint 2010 issue. Maybe I should rename this blog to Dirty solutions for exotic sharepoint issues ... :/

Note

Either way, I've also opened a case with Microsoft Support themselves, so hopefully they'll fix this in a future update or provide a better solution. Once I get more info from them, I'll let you know.