MSSQLFT Syntax School — How to Get Ahead in Search


UPDATE:


 


Download details: Microsoft SharePointPSSearch SQL Syntax (Preview)


http://www.microsoft.com/downloads/details.aspx?FamilyID=d6a10783-a4e4-4463-8444-f88be48760b3&displaylang=en


 


This "SQL Syntax" is MSSQLFT explained in near-excruciating detail, and is now included in the latest SharePoint Products and Technologies SDK.


 


===


 


So, let's say you want to perform a search against Sharepoint Portal Server 2003 programatically. 


 


Sounds simple enough, doesn't it? 


 


Naturally, you'll go straight to your handy SDK documentation and take a look at the Query Service WebService or the Microsoft.Sharepoint.Portal.Search.WebQueryService namespace -- the latter of which simply implements the former.  When you look at the Query or QueryEx method documentation for the Query Service (search.asmx), you'll see the following explanation:


“If the query string is specified with the MSSQLFT dialect, the string is interpreted per the specification for the Microsoft SQL Syntax for Full-text Search.  Query results are returned in order of relevance rank, descending from most relevant to least relevant. To specify other sort orders, you must use MSSQLFT queries and use the SQL SELECT response format.“


 


For grins, let's try doing a search on MSDN for “MSSQLFT“ and see what comes up:  MSSQLFT on MSDN


I challenge anyone to find anything useful -- at least, in terms of creating a useable search query -- in any of that documentation!  If you do find something useful, you can feel free to consider yourself clairvoyant or crazy and have yourself respectively admitted to either St. Sebastian's School for the All-Knowing or a loony bin.  So, what gives?  If we're supposed to use MSSQLFT (and, since the SDK specifically refers to it as MSSQLFT -- really rolls off the tongue, doesn't it? -- we'll assume that we are), where the heck are we supposed to learn about it?


 


Turns out there is no substantial documentation on this mysterious acronym.  The SQL 2000 documentation does cover full-text search syntax for use with SQL 2000, but it includes no information to assist with searching Sharepoint Portal Server 2003.  The old documentation for Sharepoint Portal Server 2001 does a great job of documenting search syntax (see for yourself: SPS 2001 Syntax), but it includes concepts and syntax that don't apply to the new version.


 


What's a kid to do?


 


Luckily, somebody in the product group is our friend, and they've stashed away query syntax values in a secret location previously accessible only to high-security-clearance individuals, shiny celebrity types and the like.  It doesn't quite equate to good documentation, but it's a Tips and Tricks workaround that should get you through the woods if you're simply trying to get something working. 


 


Each time a search is performed through the Sharepoint Portal Server 2003 UI, the “MSSQLFT“ query syntax is stored in a hidden input field on the results page.  Follow these steps to see what I'm talking about:


 


1. Perform a search through the SPS UI


2. View the HTML Source of page that displays the results of the search


3. Towards the bottom of the source (almost entirely at the bottom) is a hidden field with the name “schspssSQPH.“ 


4. The value of this field will contain your MSSQLFT!


 


Admittedly, this is sort of like learning how to be an automotive mechanic by randomly pulling parts out of a running engine, then trying to put them back together with a crescent wrench and some Super Glue.  However, it's the best we've got (for now).  If you perform an advanced search, you can search on specific properties and other such happy complexities;  the resulting MSSQLFT will give you an indication of how to use CONTAINS, FREETEXT, and any other predicates in your own queries.


Here's an example of a query generated in this way:


 


<hr>


SELECT


&quot;DAV:href&quot;,


&quot;DAV:displayname&quot;,


&quot;DAV:contentclass&quot;,


&quot;DAV:getlastmodified&quot;,


&quot;DAV:getcontentlength&quot;,


&quot;DAV:iscollection&quot;,


&quot;urn:schemas-microsoft-com:sharepoint:portal:profile:WorkPhone&quot;,


&quot;urn:schemas-microsoft-com:sharepoint:portal:profile:WorkEmail&quot;,


&quot;urn:schemas-microsoft-com:sharepoint:portal:profile:Title&quot;,


&quot;urn:schemas-microsoft-com:sharepoint:portal:profile:Department&quot;,


&quot;urn:schemas.microsoft.com:fulltextqueryinfo:PictureURL&quot;,


&quot;urn:schemas-microsoft-com:office:office#Author&quot;,


&quot;urn:schemas.microsoft.com:fulltextqueryinfo:description&quot;,


&quot;urn:schemas.microsoft.com:fulltextqueryinfo:rank&quot;,


&quot;urn:schemas.microsoft.com:fulltextqueryinfo:sitename&quot;,


&quot;urn:schemas.microsoft.com:fulltextqueryinfo:displaytitle&quot;,


&quot;urn:schemas-microsoft-com:publishing:Category&quot;,


&quot;urn:schemas-microsoft-com:office:office#ows_CrawlType&quot;,


&quot;urn:schemas-microsoft-com:office:office#ows_ListTemplate&quot;,


&quot;urn:schemas-microsoft-com:office:office#ows_SiteName&quot;,


&quot;urn:schemas-microsoft-com:office:office#ows_ImageWidth&quot;,


&quot;urn:schemas-microsoft-com:office:office#ows_ImageHeight&quot;,


&quot;DAV:getcontenttype&quot;,


&quot;urn:schemas-microsoft-com:sharepoint:portal:area:Path&quot;,


&quot;urn:schemas-microsoft-com:sharepoint:portal:area:CategoryUrlNavigation&quot;,


&quot;urn:schemas-microsoft-com:publishing:CategoryTitle&quot;,


&quot;urn:schemas.microsoft.com:fulltextqueryinfo:sdid&quot;,


&quot;urn:schemas-microsoft-com:sharepoint:portal:objectid&quot;


from ( TABLE Portal_Content..Scope() UNION ALL TABLE TestWeb..Scope()  UNION ALL TABLE Non_Portal_Content..Scope()  UNION ALL TABLE MyServer..Scope()  UNION ALL TABLE MyTest..Scope() ) where  ( ( ( Contains(&quot;urn:schemas.microsoft.com:fulltextqueryinfo:contents&quot;, &#39;&quot;asdf&quot;&#39;) ) ) )%__morewhereCondition__%  ORDER BY %__sort_by__%


<hr>


 


Good times!  Now, go have fun.


 

Comments (6)
  1. One negative aspect of the search functionality provides by the CMS Connector for SharePoint is there is no ability to select a particular content source or customise how the results are rendered …

  2. FG says:

    Why is it that the closer I look at Sharepoint the more obvious it becomes that it was thrown together in 5 minutes?

    Nice piece.

Comments are closed.

Skip to main content