SP 2010 - Phonetic Search Only for People Scope?

I have a custom scope I've created that I am querying against with the FullTextSqlQuery object. Recently I was asked if we could allow a phonetic search. With all the hype around this new feature in SharePoint 2010, I thought it would be very easy.

I looked, and sure enough, there is an EnablePhonetic property right on FullTextSqlQuery. I set it to true, ran my search and got back...nothing. I figured maybe it didn't like my query, which had a LIKES keyword. That wasn't it. I tried looking on the web and it seems all the mentions of phonetic search seem to be closer to press releases than coding snippets.

Finally I found this nugget on MSDN:

For FAST Search Server 2010 for SharePoint, this property is only applicable for People Search.

Well, I'm not using FAST, but I am using a custom search scope. It would appear that might be the limiting factor here. While I am searching for people, they are stored in a custom list, since they are not members of my SharePoint site. Therefore I was using a custom scope to get to them.

It would seem phonetic search is not available yet to customize in this way.

SP 2010 - Randomize the order of your search results from FullTextSqlQuery

I had a need recently to perform a query with FullTextSqlQuery but to limit the results and then display them in a random order. The FullTextSqlQuery class does support a RowLimit property, though this would not work in my case because I didn't want to get the exact same answers everytime.

Remembering that LINQ to SharePoint often requires Two-Stage Queries, I decided to employ a similar approach here and pull back the data I could then perform more culling server side.

DataTable searchResults = PerformSearch(query, queryRowLimit);
if (searchResults != null && searchResults.Rows.Count > 0)
{
 int limit = 10; //todo: this should be a webpart property
 var results = (from row in searchResults.AsEnumerable()
       orderby Guid.NewGuid()
       select row).Take(limit);
}

The PerformSearch method is just a standard setup for using FullTextSqlQuery to return a DataTable. Note that I limit my resultset as much as I can with the FullTextSqlQuery.QueryText property to try to avoid hitting a throttling exception.

With the limited results returned, I then want to randomize the order of the records. LINQ allows us to do this the same way we would in SQL, and I just order by a random Guid. Once the results are randomized, we just grab the number of records we need with the Take() extension method.

SharePoint 2010 - Can't Open Crawled Properties

I have been working on building some custom Search components, which will leverage my own Managed Properties. However, early on in the process I hit a snag.

I created some sample content and then running a full index of my farm. SharePoint was able to discover some new Crawled Properties during this process, which I was hoping to turn into Managed Properies. However, when clicking on any of the new Crawled Properties, I get an error "Unable to cast object of type 'System.DBNull' to type 'System.String'":

Using my newly working SharePoint logs, I found the following error message:

SchemaDatabase.GetSamples:Error occurred when reading [SampleUrl] System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.     at Microsoft.Office.Server.Search.Administration.SchemaDatabase.GetSamples(CrawledProperty crawledProperty, Int32 sampleCount)

I then opened up Reflector to find what was going on in that method. It turns out to be a very simple method that just calls a stored procedure. I fired up SQL Server Profiler and tracked down this call, which ultimately was breaking the page:

exec dbo.proc_MSS_GetCrawledPropertySamplesByPropertyID @CrawledPropertyId=334,@SampleCount=5

So, as it turns out, this SProc does handle NULLs, just not as robustly as we might want! Here is the SProc, which I found in the Search_Service_Application_PropertyStoreDB_{GUID} database:

CREATE PROCEDURE dbo.proc_MSS_GetCrawledPropertySamplesByPropertyID
@CrawledPropertyId  int,        
@SampleCount        int
AS
        set RowCount @SampleCount
     SELECT
           ( DP.strVal + ISNULL(cast(DP.strVal2 AS nvarchar(2000)), '') ) as 'SampleURL'
     FROM 
            dbo.MSSDocProps as DP         
        INNER JOIN 
            dbo.MSSCrawledPropSamples as CPS
            on CPS.DocId = DP.DocId
     WHERE
         CPS.CrawledPropertyId = @CrawledPropertyId
            AND DP.Pid          = 7
        ORDER BY DP.strVal
        set RowCount 0

Normally I wouldn't dream of altering a SharePoint SProc, but seeing as we are still in beta and I'm approaching a deadline, I decided to make a slight adjustment:

alter PROCEDURE dbo.proc_MSS_GetCrawledPropertySamplesByPropertyID
@CrawledPropertyId  int,        
@SampleCount        int
AS
        set RowCount @SampleCount
     SELECT
           ( ISNULL(DP.strVal,'') + ISNULL(cast(DP.strVal2 AS nvarchar(2000)), '') ) as 'SampleURL'
     FROM 
            dbo.MSSDocProps as DP         
        INNER JOIN 
            dbo.MSSCrawledPropSamples as CPS
            on CPS.DocId = DP.DocId
     WHERE
         CPS.CrawledPropertyId = @CrawledPropertyId
            AND DP.Pid          = 7
        ORDER BY DP.strVal
        set RowCount 0 

Now, when clicking on my Crawled Property, I get a proper page: