6

Search in User Profiles does not work on localized values at Sharepoint 2013

HI! I from Russia and yesterday i was trying to search user profiles by russian. For example, my last name is Мурадов and account name is demo\muradov. I can find myself by searching "demo", "muradov", but I can't find myself by "Мурадов".

enter image description here enter image description here

For search I used Manage User Profiles page from central administration and UserProfileManager.Search() method from code. I decided to figure out, why it happens.

I was decompile Microsoft.Office.Server.UserProfiles assembly and saw, that UserProfileManager.Search() method is using stored procedure dbo.proc_Profile_SearchUser.

enter image description here enter image description here

Manage User Profiles used this procedure too (I checked it with SQL Server Profiler). I used SQL Server profiler and found procedure call, it seem like

exec dbo.proc_Profile_SearchUser @partitionID='0c37852b-34d0-418e-91c6-2ac25af4be5b',@correlationId='00000000-0000-0000-0000-000000000000',@Term1=N'demo'  

And it was interesting, because

exec dbo.proc_Profile_SearchUser @partitionID='0c37852b-34d0-418e-91c6-2ac25af4be5b',@correlationId='00000000-0000-0000-0000-000000000000',@Term1=N'muradov' - retrieved my profile

exec dbo.proc_Profile_SearchUser @partitionID='0c37852b-34d0-418e-91c6-2ac25af4be5b',@correlationId='00000000-0000-0000-0000-000000000000',@Term1=N'Мурадов' - my profile not found

I open code for dbo.proc_Profile_SearchUser and start investigating... and found, that there is a table dbo.Profile_SearchUserTermStats, which store my last name as '???????', but table dbo.Profile_SearchUserTables - stores my last name as 'Мурадов'. I changed value '???????' to 'Мурадов' at dbo.Profile_SearchUserTermStats table and call dbo.proc_Profile_SearchUser - it found with search parameter as 'Мурадов'! UserProfileManager.Search() and Manage User Profiles page found me too. But, if I edit my profile and save it, my russians values will be again as '???????'.

I thought, that problem in collation of SQL Server (it was Latin1_General_CI_AS_KS_WS), and I remove SQLServer and install with collation Cyrillic_General_CI_AS. But all sharepoint databases was created with Latin1_General_CI_AS_KS_WS collation.

Now, I'm stuck. I have no idea what exactly I should do with this and I need help.

UPD. I working with Windows Server 2012 Stndart with russian language pack, also I set russian locale in OS (even is I change locale to English (US) - it doesn't help, I tried)

UPD2. I found table UserProfileValue which store profile values as sql_variant and when this column convert to nvarchar(4000) - there are right values ('Мурадов') transform to wrong ('???????'). Even if I try run following: SELECT 'Мурадов' - result as '??????', but SELECT N'Мурадов' - result as 'Мурадов' The problem still not solved.

  • 1
    Hate to break it to you, but you've committed the cardinal sin of SharePoint. Directly interacting with the database loses official support of your install by MSFT... support.microsoft.com/kb/841057 – RJ Cuthbertson Nov 2 '13 at 4:57
  • @RJCuthbertson I had problem before as I touch the database. Now, after I uninstall/install SQL Server, all my changes (and i change only 1 field in row at 1 table) lost and problem still exists. – tym32167 Nov 2 '13 at 7:11
4

I have found bug related with this behavior. Steps to fix them:

  1. Open [dbo].[proc_Profile_SetSearchUserTerms] procedure
  2. Check @changedValues declaration (varchar)
  3. Alter procedure with
    DECLARE @changedValues TABLE (PropertyValue nvarchar(255), Direction int)
  4. Be happy.
  • 1
    A word of caution for future viewers of this question: Directly interacting with the database is not officially supported by Microsoft. DO SO AT YOUR OWN RISK! Or even better, just don't. Just, please don't. – RJ Cuthbertson Nov 3 '13 at 20:14
  • >>"Just, please don't" Suggest any other solution, please. – muradovm Nov 4 '13 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.