Michael Schau

My SharePoint Blog

SPDiag 3.0 – SQL Server permissions

After installing SharePoint Diagnostic Studio 2010 (SPDiag 3.0), you might see three different Timer Jobs failing as you can read on Kirk Evans blog.

The error in the event log is

You do not have permission to run 'SP_TRACE_CREATE'

and

You do not have permission to run the RECONFIGURE statement

 

Two options are suggested. Either to disable the jobs or add the farm account to the sysadmin or sqladmin database server role.

As I don’t want to disable the Timer Jobs and also don’t want to give the farm account sysadmin rights, I instead granted the permissions the jobs actually needs and nothing more.

So, my solution is to grant the farm account ‘Alter settings’ and ‘Alter trace’ from the Permissions page under Server Properties.

AlterPermissions

You also might want to take a look at the Known issues for SPDiag on TechNet.

User Profile Service

Many have had problems provisioning the User Profile Service in SharePoint 2010, including myself.

My Powershell script failed and afterwards trying to manually create a User Profile Service Application in CA, I got the following error

An object of the type Microsoft.Office.Server.ActivityFeed.ActivityFeedUPAJob named "User Profile Service Application_ActivityFeedJob" already exists under the parent Microsoft.Office.Server.Administration.UserProfileService named "".  Rename your object or delete the existing object.

Sounds like a Timer Job, so I wanted to take a look at the job definitions.

TimerJobDefinitions

But now I got a "Operation is not valid due to the current state of the object".

OK, so I clearly needed to cleanup the Timer Jobs related to the User Profile Service. To do that I executed the following Powershell command.

Get-SPTimerJob | where {$_.name -match "User Profile Service*"} |  % { $_.Delete()}

Now I was able to both review the job definitions and provision the User Profile Service Application successfully.

PreUpgradeCheck: Missing webparts

Some of the issues in a Pre-Upgrade Check Report are straight forward to fix, but what do you do about this one? “Issue : Missing server file or server configuration issues

PreUpgradeCheck

In a site collection with thousands of sites, it is not an option to manually track down these obsolete webparts. So I wrote a SQL statement to identify the sites/pages. This statement is for the webpart with the Id ‘34febc2f-1526-47d8-d72a-7b992ac65a7d’.

SELECT [AllDocs].*
FROM [WSS_Content].[dbo].[AllDocs] INNER JOIN [WSS_Content].[dbo].[WebParts]
ON [AllDocs].Id = [WebParts].[tp_PageUrlID]
WHERE [WebParts].[tp_WebPartTypeId] = '34febc2f-1526-47d8-d72a-7b992ac65a7d'

Now you have a nice list of the pages where you need to remove the webpart.

PreUpgradeCheck2

Go to the Web Part Page Maintenance (<url>?contents=1) and delete the webpart.

PreUpgradeCheck3

SPCalendarView - order of SPCalendarItems

When adding SPCalendarItems to the SPCalendarItemCollection you need to be carefull about in which order you add them. If SPCalendarItems are added in a specific order, they doesn’t show in Month view.
In the example below Test User 1 doesn’t show in the week starting Septemper 21st. In this week the calendar item for Test User 2 is added to the collection after Test User 1.
If the items are added in reverse order as they are in the week starting September 14th, both items are visible in Month view as expected.

If you however switch to Week view Test User 1 is visible. It is also visible in Day view.
So it's not because it doesn't exist in the collection, it just doesn't show in Month view.

Here's some code to reproduce the issue.

private DataTable GetHolidayData()
{
    DataTable holidayData = new DataTable("HolidayData");
    holidayData.Columns.Add("ID", Type.GetType("System.String"));
    holidayData.Columns.Add("Organizer", Type.GetType("System.String"));
    holidayData.Columns.Add("FromDate", Type.GetType("System.DateTime"));
    holidayData.Columns.Add("ToDate", Type.GetType("System.DateTime"));
    holidayData.Columns.Add("IsAllDayEvent", Type.GetType("System.Boolean"));

    DataRow row = holidayData.NewRow();

    row[0] = "test1@test.com";
    row[1] = "Test User 1";
    row[2] = "2009/09/23";
    row[3] = "2009/09/25";
    row[4] = true;

    holidayData.Rows.Add(row);

    DataRow row2 = holidayData.NewRow();

    row2[0] = "test2@test.com";
    row2[1] = "Test User 2";
    row2[2] = "2009/09/21";
    row2[3] = "2009/09/25";
    row2[4] = true;

    holidayData.Rows.Add(row2);
    // row2 will not show in month view

    DataRow row3 = holidayData.NewRow();

    row3[0] = "test2@test.com";
    row3[1] = "Test User 2";
    row3[2] = "2009/09/14";
    row3[3] = "2009/09/18";
    row3[4] = true;

    holidayData.Rows.Add(row3);

    DataRow row4 = holidayData.NewRow();

    row4[0] = "test1@test.com";
    row4[1] = "Test User 1";
    row4[2] = "2009/09/16";
    row4[3] = "2009/09/18";
    row4[4] = true;

    holidayData.Rows.Add(row4);
    // adding a calendar item with a shorter duration last, 
    // will make it visible in month view.

    return holidayData;
}

private SPCalendarItemCollection GetCalendarItems(DataTable results)
{
    SPCalendarItemCollection items = new SPCalendarItemCollection();
    using (SPWeb web = SPContext.Current.Web)
    {
        foreach (DataRow row in results.Rows)
        {
            SPCalendarItem item = new SPCalendarItem();

            item.Title = row["Organizer"] as string;
            item.StartDate = (System.DateTime)row["FromDate"];
            item.EndDate = (System.DateTime)row["ToDate"];
            item.hasEndDate = true;
            item.IsAllDayEvent = (System.Boolean)row["IsAllDayEvent"];
            item.CalendarType = Convert.ToInt32(SPCalendarType.Gregorian);

            items.Add(item);
        }
    }

    return items;            
}

So how do we workaround it? As mentioned in the beginning it depends on the order of items.
Sorting on FromDate ascending will make all items visible in Month view. In my sample code above we can easily sort using a DataView.

    DataView v = results.DefaultView;
    v.Sort = "FromDate ASC";
    results = v.ToTable(); 

User Profile multivalue field problem in search results

A while ago I had this problem where the People search results displayed in the ‘People Search Core Results’ web part & "Search High Confidence Results" web parts didn't  show all the Skills from the User Profile. Only the last skill were returned from the search engine. I had the same behaviour when searching for people through the SharePoint search web service.

In the User Profile, as you can see below, multiple skills had been added to the SPS-Skills property. Notice that the last one is "Test Theory".



So when I did a serch for this user, I got the following XML output.

 

As you can see, only the last value was returned.

So what was causing this?

Well, after a lot of troubleshooting I found that this behaviour can occur if you edit the Skills Managed property using the Search Settings. When you edit this property or simply click the OK button on the Shared Services Administration: SSP > Search Administration > Managed Properties > Edit Managed Property > Edit Managed Property – Skills, the field "HasMultipleValues" within the SSP database table MSSManagedProperties, gets updated. By default the value for this field is 1, and it is updated to 0 even though this field does not map to any user editable properties on the SharePoint user interface.

Microsoft have confirmed this "issue", but do NOT consider it a bug. Yell
Update: Microsoft have changed their mind and now consider it a bug. No date for a QFE has been set.

Fortunately there's a way to reset HasMultipleValues to 1.

1. Create a custom dummy multivalue User Profile property and add it to the Skills Managed property.
2. Ensure that “Include values from all crawled properties mapped” is set.
3. Click OK.

 

Now the ‘HasMulitpleValues’ fiels is set to 1.

Do a full crawl. Executing the initial query again now returns all values from the Skills property.

Wrong wordbreaker on Index Server

Recently I noticed that I didn't get the expected search results when querying for data indexed through BDC.

The search term was System.ArithmeticException and I knew for sure that this term existed in the BDC datasource, but I got no results. I started investigating, but I'll spare you that story and instead tell you why this happened.

In order to get correct search behaviour you need to make sure that the same language specific wordbreaker is used both on query and indexing time. So if you're indexing English content your query wordbreaker should also be English. MOSS is using the browsers language settings to determine what wordbreaker to use.

     

On indexing time it's a different story. The iFilter checks if the text chunk from a document (or BDC source) has Locale info. If yes, it'll use the wordbreaker assosiated with that Locale.

If it doesn't contain Locale info (the BDC datasource didn't) the System Locale of the Index Server is used. In this case the Regional Settings on the Index Server was set to Danish, meaning that the BDC source was indexed using the Danish wordbreaker. Outch!! Yell

I did a quick test and put the text System.ArithmeticException into a Word document and indexed it. No problem, I got hit on it when searching. I tried the same on a txt document. This time I didn't got a hit because a txt file doesn't contain Locale info.

Changing the Regional Settings to English and reindexing all content solved the problem...

Now you know what it means if the Regional Setting of your Index Server is NOT set to English. Of cource in some cases it might makes sense to set to something other than Ensligh, but not is this case.