Wednesday, April 14, 2010

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:

No comments:

Post a Comment