Bug in the UseCount property of the CoreProperty class

The UseCount property would be very useful, by definition it “gets the number of times this property is used in the user profile database.” It would help you to decide whether you can delete the property without data loss or there are profiles already that utilize the property.

When you try to use this property you get an “UserProfileException was unhandled” warning in Visual Studio that means the following:
A failure was encountered while retrieving the profile count. SQL error occurred.
Microsoft.Office.Server.UserProfiles.UserProfileException
at Microsoft.Office.Server.UserProfiles.CoreProperty.get_UseCount()

The getter of the property calls the profile_GetProfileCountWithProperty in the Profile DB database (the database you configured for the User Profile Service Application) stored procedure. When the stored procedure does not find the specified profile property by its name, it returns the Error output parameter having value 1. In this case a PropertyNotDefinedException is thrown in the try/catch block of the UseCount property getter and the exception is re-thrown as UserProfileException.

My first idea was that I might try to get the UseCount for a CoreProperty that is section type, but I was wrong.

Creating a quick SQL trace and checking the SharePoint log both showed, that the code of the UseCount getter does not provide the mandatory partitionID parameter for the stored procedure.

Here is the summary of the exception from the log file:

System.Data.SqlClient.SqlException: Procedure or function ‘profile_GetProfileCountWithProperty’ expects parameter ‘@partitionID’, which was not supplied.     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()     at System.Data.SqlClient.SqlDataReader.get_MetaData()     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)     at System…    
….Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)     at Microsoft.Office.Server.Data.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)    
at Microsoft.Office.Server.Data.SqlSession.ExecuteReader(SqlCommand command, Boolean retryForDeadLock)     at Microsoft.Office.Server.UserProfiles.CoreProperty.get_UseCount()

As a dirty workaround I modified the stored procedure (of course, I’ve created a backup of the original version first) to have the default value for the mandatory partitionID parameter. It is not very hard, since there is only a single GUID value in the PartitionID field of the PropertyList table of the profile database.

…
ALTER PROCEDURE [dbo].[profile_GetProfileCountWithProperty]
    @partitionID uniqueidentifier = ‘0C37852B-34D0-418E-91C6-2AC25AF4BE5B’,
    @PropertyName nvarchar(50),
…

Be sure to use the correct value for your system:

SELECT DISTINCT [PartitionID]
  FROM [Profile DB].[dbo].[PropertyList]

After this hack the UseCount property works like a charm, but I hope this bug will be fixed in the final version.

Advertisements