Lars Fastrup on SharePoint Search

Using MOSS 2007 to index and search Lotus Notes databases

Microsoft Office SharePoint Server 2007 ships with a protocol handler for indexing Lotus Notes databases. But it requires some post-installation steps to enable. Mapping Lotus Notes user identification numbers to Windows users is also a little tricky - but possible to enable security trimming of Lotus Notes search results. However, the entire topic of using SharePoint for indexing Lotus Notes databases has always seemed to be poorly documented. I have at least never been able to find a lot of useful information online on the topic. But today I just stumled upon three Microsoft blog post that sheds a lot more light on this topic.

How to setup the MOSS 2007 Protocol Handler for Lotus Notes to search multiple Domino Databases. Part 1 of 3
How to setup the MOSS 2007 Protocol Handler for Lotus Notes to search multiple Domino Databases. Part 2 of 3
How to troubleshoot MOSS 2007 Protocol Handler for Lotus Notes and other tips/tricks. Part 3 of 3

The third and last article in the series provides information about important hotfixes for the Lotus Notes protocol handler.

New book on SharePoint search

I have not blogged about this before but I have recently joined forces with Patrick to write a new book on MOSS 2007 search. Yes, an entire book dedicated to the topic of MOSS search as well as WSS search!

The book has already been announced by Microsoft Press here. It can also be pre-ordered on Amazon.

Blog Moving Day

I am pleased to announce that I have decided to officially move my SharePoint search blog to SHAREPOINTSearch.com. I would like to thank Christopher for providing the infrastructure for this blog.

SharePoint Solution Installer source code released!

Here is some good news for all of you who asked for the source to my popluar SharePoint Solution Installer. I have now moved it to CodePlex as the project:

http://www.codeplex.com/sharepointinstaller

I am really short on time to improve it any further in the near future. But I will be happy to invite interested contributors to the project on CodePlex. Just use my contact form on CodePlex to ask for participation.

The secrets of SQL Syntax Queries for Relevant Results in MOSS 2007 Enterprise Search

This post is a description of how to use the Microsoft.Office.Server.Search.Query.FullTextSqlQuery class for searching with the MOSS 2007 search engine using the Enterprise Search SQL syntax. I will at the end of this posting explain how to execute queries for optimal results rankings. 

But let me first establish the basics of using the FullTestSqlQuery class. You will only need to employ it for one of the following reasons:

• You need to support Wildcard searches.
• You need to search with date ranges.
• You need to search properties with different operators (CONTAINS, =, >=, <=, <, >, LIKE).
• You need to search properties for NULL values.
• You need to use the NEAR operator.
• You need nested Boolean queries.
• You like the most flexible (or complicated ;-) ) solution.

Simple keyword and property queries are easier done with the Microsoft.Office.Server.Search.Query.KeywordQuery class. I will, however, not cover this class here.

Ok, let us get started. I have outlined the format of the SQL Syntax accepted by the MOSS 2007 search engine below. After that I will show how to talk to the search engine via the official .NET API.

SELECT <columns>
FROM <content source>
WHERE <conditions>
ORDER BY <columns>

SQL Syntax Examples

1. Finds relevant results containing the keyword SharePoint.
   
   

   SELECT WorkId,Path,Title,Write,Author,HitHighlightedSummary,
          HitHighlightedProperties,CollapsingStatus
   FROM Scope()
   WHERE FREETEXT(defaultproperties, 'SharePoint') 
   ORDER BY Rank Desc

2. Finds relevant results containing at least one of the keywords SharePoint and Search.
   

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, 'SharePoint Search') 
   ORDER BY Rank Desc

3. Finds relevant results containing both the keywords SharePoint and Search.

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, '+SharePoint +Search') 
   ORDER BY Rank Desc

4. Finds relevant results containing the exact phrase SharePoint Search.

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, ' "SharePoint Search" ') 
   ORDER BY Rank Desc

5. Finds relevant results containing both the keywords SharePoint and Search but not the keyword WSS. 

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, '+SharePoint +Search -WSS') 
   ORDER BY Rank Desc

6. Finds relevant SharePoint results authored by persons named John.
   

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, 'SharePoint') AND CONTAINS(Author,' "John" ')
   ORDER BY Rank Desc

7. Finds relevant SharePoint results modified within the last 30 days.
   

   SELECT WorkId,Path,Title,Write,Author,...
   FROM Scope()
   WHERE FREETEXT(defaultproperties, 'SharePoint') AND Write<=DATEADD(DAY,30,GETGMTDATE())
   ORDER BY Rank Desc

 .NET API
The following code should give you some inspiration how to execute a SQL query against the MOSS 2007 search engine.

// Execute Query
ResultTableCollection results = null;
using (FullTextSqlQuery query = new FullTextSqlQuery(ServerContext.Current))
{
  query.StartRow = 0;
  query.RowLimit = 10;
  query.HighlightedSentenceCount = 3;
  query.EnableStemming = true;
  query.TrimDuplicates = true;
  query.Culture = CultureInfo.CurrentCulture;

  query.KeywordInclusion = KeywordInclusion.AnyKeyword;
  query.SiteContext = new Uri("http://yourserver/sites/asite");




  if (SPSecurity.AuthenticationMode != AuthenticationMode.Windows)

    query.AuthenticationType = QueryAuthenticationType.PluggableAuthenticatedQuery;
  else

    query.AuthenticationType = QueryAuthenticationType.NtAuthenticatedQuery;
  query.QueryText = "SELECT ... FROM Scope() WHERE ... ORDER BY ..."; 
  results = query.Execute();
}

// Parse results and create XML output
StringBuilder buffer = new StringBuilder(10240);
ResultTable relevantResults = results[ResultType.RelevantResults];
using (XmlTextWriter writer = new XmlTextWriter(new StringWriter(buffer)))
{
  writer.Formatting = Formatting.Indented;
  writer.WriteStartElement("Results");
  writer.WriteAttributeString("hits", relevantResults.TotalRows.ToString());
  while (relevantResults.Read())
  {
    writer.WriteStartElement("Result");
    for (int i=0; i<relevantResults.FieldCount; i++)
    {
      writer.WriteStartElement(relevantResults.GetName(i));
      object val = relevantResults.GetValue(i);
      if (val != null)
        writer.WriteString(val.ToString());
      else
        writer.WriteString("null");
      writer.WriteEndElement();
    }      
    writer.WriteEndElement(); 
  }
  writer.WriteEndElement(); 

  string xml = buffer.ToString();
  ...






}

The using statement around the usage scope of the FullTextSqlQuery class is very important to remember as you will otherwise get an OutOfMemoryException after running a good number of queries. This is so because the search engine is a COM server that the .NET API maintains a handle to.

A closer look at the FREETEXT predicate
Take a look at following two keyword searches using the FREETEXT predicate (rest of SQL string omitted for simplicity):

FREETEXT(defaultproperties, '+sharepoint +search')

FREETEXT(defaultproperties, 'sharepoint') AND FREETEXT(defaultproperties, 'search')

Both queries are valid and yield the same results buth with different ranking. Which one yields the best results ranking? Answer: The first one! It is recommended that you only use one FREETEXT predicate in a search query. Results ranking will otherwise not be optimal.

The defaultproperties keywords references the default set of properties to include in the ranking algorithm. This is also recommended for optimal ranking of results. You can alternatively use the WITH predicate to define your own set of properties if you are not happy with the default one. This is helpful if you need to promote results containing the keywords on your own custom properties. Note: The standard MOSS 2007 search center simply uses the defaultproperties.

The FREETEXT predicate can also be configured for implicit AND search or implicit OR search. This means that a query like:

FREETEXT(defaultproperties, 'sharepoint search')

can respectively find results containing both the keywords sharepoint and search or find results containing at least one of the keywords. Use the the KeywordInclusion property on the FulltextSqlQuery object to control this behavior. See the code example earlier in this posting.

Conclusion
I have just described the key concepts of using the FulltextSqlQuery class. I have not described all of its features nor have I described the SQL Syntax in full. I refer you to the MOSS 2007 SDK for a complete reference.

Stay stuned for more postings on the inner workings of the MOSS 2007 search engine!

New community site sharepointsearch.com

New SharePoint search blogger

It is always a pleasure to welcome new SharePoint Seach bloggers and so I warmly welcome and recommend Christopher Even's new blog on SharePoint Enterprise Search.

He is very knowledgeable about SharePoint Search with many big Enterprise Search Developments behind him. He is furthermore a top expert on Protocol Handlers and IFilters having developed a generic protocol handler for SharePoint 2003 and 2007. I have seen it in action and it can literally index content (not just data as the BDC) from any external system with full security mapping and the lot. Very cool stuff!! I hope he will blog more about it some day.

Ontolica Video Tutorials

Our technical writer just finished the first Ontolica video tutorial, explaining the process of installing Ontolica for MOSS 2007 and creating a new Ontolica search center. We will soon post more tutorials here.

Our technical writer, Karl Maybach, also wrote an excellent administrators guide for Ontolica. It is included in the Ontolica download. He is a freelance writer and the best one you could ever hire. You basically just have to show him your product once and vaguely explain what it does - and he will quickly crank out some excellent manuals teaching you stuff about your own product that you never even realized yourself.

Ontolica for MOSS 2007 RTM

I am together with the rest of Mondosoft proud to announce the RTM release of Ontolica for MOSS 2007. This is a great milestone for us that deserves to be celebrated and our internal release party will therefore bring us to the Noerrebro Brew House tonight for a great Beer experience....

In brief the product simply offers an improved search UI on top of the MOSS 2007 search engine. It is also a major upgrade of our first SharePoint search add-on Ontolica for SharePoint Portal Server 2003. We released the first beta of Ontolica for MOSS 2007 back in February this year and more than 100 companies signed up as a beta tester. Thank you all for your participation, I would like to thank all of you for the great feedback you have provided to us during the beta testing period. 

Licensing
Ontolica ships with two built-in licenses to get you started using the product right away. The first license is the Free Evaluation license and the second one is the Free Wildcard license. The evaluation license gives you full functionality for an unlimited time period but for one server and two unique users only. The Wildcard license is a free production license that will give you limited functionality for an unlimited number of servers and users and with no expiration date. You may also request a 30 day trial license if you need to evaluate the product with full functionality and unlimited servers and users.

The free Wildcard license will basically give you all standard SharePoint search features + wildcard search. A production license for Ontolica with full functionality requires you to purchase a license from our sales team.

Features
Here is the full list of features included in the RTM release. We did a while back decide to take out a few of the features I mentioned here in February. They were postponed in favour of new features like implicit wildcard search and sorting results by custom properties plus some internal features. But not to worry - we will continue improving the product and add the promised features along with other great features requested by our customers and partners. I hope to be able to post a feature road map soon.

• All the standard search features to be found in MOSS 2007.
• Improved Boolean search (AND, OR, NOT)
• Wildcard (*) search.
• Implicit Wildcard search option. Enabling this option removes the need for the user to type * at the end of a keyword in order to run a wildcard query. This option is intended for people search only.
• NEAR search.
• Consistent search across MOSS and WSS sites. The scopes "This site" and "This list" are now fully configurable and will take the user to the Ontolica search center instead of the WSS search page in the /_layouts folder.
• Site dependent search tab configurations. Each search tab can be configured with its own set of searchable properties, search result properties, sorting properties, drill down properties, quick filters, search scopes and search result actions. All of these settings can additonally be inherited or overwrited down through the SharePoint taxonomy, i.e. you may configure search tabs on the Farm level, the web application level, the site collection level all the way down to the individual site.
• Site dependent search box configuration.
• Complete web based configuration UI.   
• Federated search. This feature will basically enable you search multiple SSP's from within the same Ontolica search center.
• Improved meta data search. Add your own meta data to the advanced search page with a few clicks. Select between a text box, a dropdown box or a lookup dialog for value input. 
• Display custom properties in search results with a few clicks.
• Configurable action menu on results. Ontolica will include a fully configurable SharePoint drop down menu on search results. The default actions that ship with Ontolica are:
  • View Propeties (teleports the user to the document library list item representing the document).
  • Edit in Microsoft Office Word / PowerPoint / Excel.
  • Alert Me
  • Add to My Links
  • View Details.
• Drill Down. Provides the user with a dynamic list of suggestions for refining his or her search. It basically works by analyzing the meta data on the search results. The list of meta data (properties) to provide suggestions for are also fully configurable. Ontolica will in the default configuration be able to provide suggestions for Sites, Authors, File types and Content types.
• Quick filters. Will allow you to easily configure some expression based filters that appear as radio buttons below the keyword input field.
• Sort results by custom property. Sort results by relevance, date, title, or any other property available in the search index.
• Document details page.
• Improved people search. Basically nicer thumbnails and more info displayed on each result.
• XSLT enabled web parts. All Ontolica search web parts are XSLT enabled to give you full control of the look & feel when required. Even the Ontolica search box in the upper right corner is XSLT enabled. You will, however, only need to worry about modifying the default Ontolica XSLT templates if you have needs that go far beyond common customization scenarios. You can really configure and customize Ontolica to a great extent before you need to worry about XSLT, which I know many of you do not really want to or have time to.
• Configurable web parts. All Ontolica web parts have a dedicated configuration page where you can easily configure the various options of the web part.
• Customizable search dialog. Create your own custom search forms.
• Plugs into the SharePoint feature framework. Ontolica search can be activated/deactivated on the site collection level.
• Search Center site templates for easy creation of new Ontolica search centers.
• Solution based installation process. Ontolica installation and deployment relies on the new solutions framework in Windows SharePoint Services V3.

SharePoint Solution Installer

Here ya go - a free tool for eased installation and deployment of SharePoint 2007 solution files to a SharePoint server farm.

Version 1.0.3 Download: SharePointSolutionInstaller_V1_0_3.zip

The tool is FREEWARE and provided AS IS. Please comment on this post to report any issues.

** UPDATE March 25^th 2007: New version 1.0.3 release. It includes a work-around for the daylight saving time (DST) bug in the SharePoint 2007 timer service, which caused the solution deployment and retraction jobs to not run until one hour after being created. The work-around basically instructs SharePoint to run the job yesterday. This will immediately kick start the timer job no matter what time zone and DST settings your server might have. Microsoft is aware of this bug and has published a knowledgebase article about it. I think we can safely assume WSS V3 SP1 will include a fix for this issue.

** UPDATE March 15^th 2007: Renamed product from Generic SharePoint 2007 Solution Installer to SharePoint Solution Installer (SSI). How is that name Patrick? Maybe the single malt can power an even better name next week....

I donate this tool to the SharePoint community as I have lately come to the conclusion that you sometimes need a more user friendly and safer way of installing wsp solution files to a SharePoint farm. You espceially need it when you want to distribute a SharePoint add-on as a solution to your customers. You cannot rely on everybody out there in the wild west to be intimately familiar with solution deployment, not to mention interpreting all the error conditions that can potentially arise. Distributing a raw solution file together with a simple install.bat script will easily earn you a lot of unnecessary support cases. Trust me, I learned this the hard way.

Hence, I was motivated to develop a nice and almost fool proof solution installer for our Ontolica for SharePoint product. I ended up developing a very generic and configurable version of it. I thought it could also come in handy for other SharePoint's and quickly decided to share it with you. 

The SharePoint Solution Installer includes the following features:

• Support for all relevant operations:
  • Add solution to the SharePoint solution store.
  • Deploy solution to one or more web applications.
  • Upgrade solution.
  • Retract and remove solution.
• Pre-install system check for the following conditions:
  • WSS V3 is installed.
  • MOSS 2007 is installed (This check can be disabled in config file),
  • User has permission to install solutions.
  • SharePoint database is online.
  • WSS Administration service is started.
  • WSS Timer service is started.
• Implements a work around for the following error conditions:
  • Another solution deployment job never finished and is now blocking for the creation of a new job. Work-around: Delete old job from the list of Timer job defnitions.
  • Timer service is not started: An attempt is made to start it (please note that timer services on other front ends will not be started).
  • Database is offline, which will yield an ugly NullreferenceException from SharePoint offering little clue to the root cause of the problem. Work around: Detect NullreferenceException and display nice error message.
• Display EULA. (Can be disabled in config file). 
• Web application list where user can select the web apps. to deploy the solution to.
• Rollback after installation errors.
• Configurable Product Title.
• Configurable banner image.
• Configurable logo image.

Here are some screen shots that will give you a better idea of the nature of the tool (Don't worry about the Mondosoft logo, you can remove it or put in your own in the config file) :

 The installer can be launched with setup.exe, which can in turn be configured with the setup.exe.config file. The following configuration example outlines the various options that you can configure.

<configuration>
  <appSettings>
    <add key="BannerImage" value="Default"/>
    <add key="LogoImage" value="Default"/>
    <add key="EULA" value="EULA.rtf"/>
    <add key="Require" value="MOSS"/>
    <add key="FarmFeatureId" value="A69D3FDA-142A-4d2c-BA6D-446CE01FF1C2"/>
    <add key="SolutionId" value="44906C46-D7C1-4a14-A7D2-87394D9FC7E3"/>
    <add key="SolutionFile" value="yoursolution.wsp"/>
    <add key="SolutionTitle" value="Enter Product Title Here"/>
    <add key="SolutionVersion" value="1.0.0.0"/>
    <add key="UpgradeDescription" value="Upgrades {SolutionTitle} on all frontend web servers in the SharePoint farm. "/>
    <add key="RequireDeploymentToCentralAdminWebApllication" value="true"/>
    <add key="RequireDeploymentToAllContentWebApplications" value="false"/>
  </appSettings>
</configuration>

Below is a brief explanation of each configuration option:

BannerImage
Accepts a path to your own banner image. The image is automatically stretched to fir the title bar. Remove or specify empty value to omit banner image.

LogoImage
Accepts a path to your own logo image. Remove or specify empty value to omit banner image.

EULA
Accepts a path to your own EULA file. Remove or specify empty value if you do not want to present an EULA to the user.

Require
Specify MOSS if your solution requires Microsoft Office SharePoint Server 2007 to install and run. Remove or specify empty value if your solution will settle for a WSS only farm.

FarmFeatureId
Specify the GUID of the farm level feature that the installer should automatically activate at the end of the installation process. Specify empty value if the installer should not activate any feature.

SolutionId
Specify the GUID of your solution. The installer will need this to detect any existing solution in the SharePoint solution store.

SolutionFile
Specify the filename of the solution file, e.g. mysolution.wsp. Creating the solution file is of course your job entirely - but I can highly recommend you to take a look at the Visual Studio 2005 Extensions for WSS 3.0

SolutionVersion
Specify the version of your solution. The upgrade option will only show if this version number is different from the previously installed solution.

The remaining options should be self explanatory.

Mike Taghizadeh covering MOSS 2007 Search Capabilities

I just stumbled upon a great blog about MOSS 2007 Search by Mike Taghizadeh. I can recommend reading his two latest posts on stemming in the MOSS 2007 search engine.

MOSS Search Word Stemming - Part 1
MOSS Search Word Stemming - Part 2

SharePoint Explorer for WSS3

I learned that Patrick once again promoted our free SharePoint Explorer tool. This time on the Microsoft European SharePoint Conference 2007 in Berlin this week. Thanks Patrick

SharePoint Explorer has until now only been available for SharePoint 2003. But I once recompiled it for 2007 and sent Patrick a copy, which is the one he showed in Berlin. So now I better make it available to all of you.

You can download it here: SharePointExplorer_V1_5_1_For_WSS3.zip

**UPDATE March 9th 2007: Fixed bug where SharePoint Explorer failed to launch at all.

INSTALLATION
The zip archive contains a single EXE file that you must run directly on your WSS V3 server. The tool does not support remote connections to WSS servers,

IMPORTANT NOTES
The tool is just a recompile of our existing tool for SharePoint 2003. It does therefore not include any support for all the new cool features in the object model like features, content types, event handlers, workflow, recycle bin, etc. The tool is only good for exploring Virtual Servers, Site Collections, Sites, Lists and Files.

I am, however, working on a new and much better version that will recognize the new stuff in the object model. It will additionally also allow for remote access, which has been the #1 feature request since we released the first version for SharePoint 2003.

First Ontolica Screenshots

I have been asked a few times this week for some screen shots of the upcoming release of Ontolica Search for MOSS 2007. I have therefore decided to post a few screenshots now to give you an idea of how the new Ontolica is going to look like.

I have only picked four screen shots for now. The first three will give you and idea of the Ontolica search experience and the fourth one an idea about the configuration experience. The latter illustrates a configuration wizard for easy management of the property filters on the advanced search page. There are several other configuration wizards like one for configuring the actions menu on search results shown in the second screen shot.

The screen shots only represent one part of the complete Ontolica product - there are many more screen shots to come.

Ontolica Beta Program

We are now preparing a beta program for our new product Ontolica Search for MOSS 2007. We plan for a two month beta period with the following milestones:

Beta 1 on February 22nd
This release will only be available to selected beta testers. But we are still accepting requests for joining the beta program, You can sign up by sending an email to beta@ontolica.com. You wíll in turn receive an installation package plus some preliminary documentation on the 22nd. We will also arrange a live meeting online for all beta testers to give an overview of the features and how to use them.

Beta 2 on March 22nd
Second release with the improvements based on feedback from the field testing. We are still unsure whether this release will be available to the public.

Release Candidate on April 22nd and final release shortly thereafter.

Supported languages
Ontolica Search for MOSS 2007 will only be available in an English language edition during the beta period. We will send the product out for translation immediately after the final release becomes available. You can expect us to do translations for German, French and Spanish. We will consider other languages on request. You can, however, also translate the necessary UI strings yourself as they will all be available in standard RESX format.

Microsoft European SharePoint Conference 2007

I will attend the SharePoint 2007 conference in Berlin next week together will a few other Mondosofters. We will have a booth in the exhibition area where we will be able to give you a preview of the upcoming Ontolica Search for MOSS 2007.

I am also going there as a speaker. I will present the Microsoft session IT06 - Search Technical Drilldown on day 2 at 11:30. You should attend this session if you would like to get a better overview of all the great improvements to be found in MOSS 2007 search. The session will also include a few demos on customizing various aspects of the MOSS 2007 search.