Derek Fakehany very kindly send me a lot of detail on this.

 

He praised in his note to me the fix that Carl Lewis had posted in the WSS newsgroup which made him realise that Boolean searches were possible.

 

The solution below is however a) put in the kind of detailed way that a FAQ item needs and b) includes Boolean searches of Lists which Carl's solution didn't.

 

Here are Derek's steps -

 

1. Get into your Windows Sharepoint Services (WSS) box

 

2. Fire up SQL Enterprise Manager

 

3. Work your way down to your database(s) for Sharepoint

 

4. Open your database and then open the Stored Procedures for the database

 

5. To enable Boolean for document libraries, look for the stored procedure called “proc_FetchDocSearchResults”

 

6. Make a backup of the procedure (I just right-clicked, selected copy and pasted it into a Notepad document).

 

7. Right-click on the stored procedure and select “Properties”

 

8. You’ll get a window with the procedure outlined. Make the following changes:

 

a. Add the following lines to the procedure before the line "SET NOCOUNT ON":
      DECLARE @CustSearchTerm nvarchar(255)
      SET @CustSearchTerm = '"' + @SearchTerm + '"'

 

b. Find the word “FreeTextTable” and replace it with “ContainsTable”

 

c. In the same line change the variable “@SearchTerm” to “@CustSearchTerm”

 

9. Hit Apply & OK

 

10. To enable Boolean for lists, look for the stored procedure called “proc_FetchListItemSearchResults”

 

11. Make a backup of the procedure

 

12. Right-click on the stored procedure and select “Properties”

 

13. Make the same changes in Step #8 above – HOWEVER, note that there are MULTIPLE OCCURRENCES (three to be exact) of FreeTextTable and @SearchTerm. You need to change ALL THREE OCCURRENCES.

 

14. Hit Apply & OK.

 

15. Repeat for each Sharepoint database you may have

 

16. Close the Enterprise Manager

 

17. Run IISRESET

Note that the new search only supports and/or but that's all I really need. And it will ONLY work if you actually specify and/or in your search (i.e. “testing AND tips” rather than “testing tips”).

 

 

----------------

 

 

 

Basically I created two alternative scripts (proc_FetchDocSearchResultsBoolean and proc_FetchListItemSearchResultsBoolean).

 

If the search text contains an ‘ AND ‘ or an ‘ OR ‘ the search is sent from the original code to the new Boolean counterparts. Thus leaving the search behavior the same until a Boolean is entered.

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_FetchDocSearchResults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_FetchDocSearchResults]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_FetchDocSearchResultsBoolean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_FetchDocSearchResultsBoolean]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_FetchListItemSearchResults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_FetchListItemSearchResults]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_FetchListItemSearchResultsBoolean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_FetchListItemSearchResultsBoolean]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE proc_FetchDocSearchResults
(
                  @SearchTerm nvarchar(255),
                  @SiteId uniqueidentifier,
                  @WebId uniqueidentifier,
                  @RankPaging int,
                  @ModifiedPaging datetime,
                  @ItemIdPaging uniqueidentifier)
            AS
            
                  IF CHARINDEX(' OR ', @SearchTerm) > 0 OR CHARINDEX(' AND ', @SearchTerm) > 0
                  Begin
                    EXEC proc_FetchDocSearchResultsBoolean @SearchTerm, @SiteId, @WebId, @RankPaging, @ModifiedPaging, @ItemIdPaging
                    RETURN
                  End
            
                  SET NOCOUNT ON
                  SELECT
                        NULL,
                        NULL,
                        NULL,
                        Docs.DirName,
                        Docs.LeafName,
                        Docs.DocLibRowId,
                        Docs.ListId,
                        Lists.tp_Title,
                        UserData.tp_Author,
                        UserData.tp_Editor,
                        UserInfo.tp_Title,
                        Docs.TimeLastModified AS LastModified,
                        Docs.[Size],
                        Docs.Id AS ItemId,
                        Docs#CT.Rank AS CT#Rank,
                        CASE WHEN (Docs.Type = 1)
                             THEN 3
                             ELSE 2
                        END
                  FROM
                        Docs
                  INNER JOIN
                        FreeTextTable(Docs, *, @SearchTerm) AS Docs#CT
                  ON
                        Docs#CT.[Key] = Docs.ID
                  LEFT OUTER JOIN
                        Lists
                  ON
                        Docs.ListId = Lists.tp_ID
                  LEFT OUTER JOIN
                        UserData
                  ON
                        (Docs.ListId = UserData.tp_ListId) AND
                        (Docs.DocLibRowId = UserData.tp_ID)
                  LEFT OUTER JOIN
                        UserInfo
                  ON
                        (UserData.tp_Editor = UserInfo.tp_ID) AND
                        (UserData.tp_SiteId = UserInfo.tp_SiteID)
                  WHERE
                        @SiteId = Docs.SiteId AND
                        @WebId = Docs.WebId AND
                        ((Lists.tp_Flags IS NULL) OR
                         ((Lists.tp_Flags & 0x400) = 0) OR
                         (UserData.tp_ModerationStatus IS NULL) OR
                         (UserData.tp_ModerationStatus = 0)) AND
                        ((Docs.Type = 0) OR
                         ((Docs.Type = 1) AND
                          (Docs.DoclibRowId IS NOT NULL))) AND
                        ((@RankPaging IS NULL) OR
                         (Docs#CT.Rank < @RankPaging) OR
                         ((Docs#CT.Rank = @RankPaging) AND
                          ((Docs.TimeLastModified < @ModifiedPaging) OR
                           ((Docs.TimeLastModified = @ModifiedPaging) AND
                            (Docs.Id > @ItemIdPaging)))))
                  ORDER BY
                        CT#Rank DESC,
                        LastModified DESC,
                        ItemId ASC
                  RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE proc_FetchDocSearchResultsBoolean
(
                  @SearchTerm nvarchar(255),
                  @SiteId uniqueidentifier,
                  @WebId uniqueidentifier,
                  @RankPaging int,
                  @ModifiedPaging datetime,
                  @ItemIdPaging uniqueidentifier)
            AS
            
                  DECLARE @CustSearchTerm nvarchar(255)
                  SET @CustSearchTerm = '"' + @SearchTerm + '"'
            
                  SET NOCOUNT ON
                  SELECT
                        NULL,
                        NULL,
                        NULL,
                        Docs.DirName,
                        Docs.LeafName,
                        Docs.DocLibRowId,
                        Docs.ListId,
                        Lists.tp_Title,
                        UserData.tp_Author,
                        UserData.tp_Editor,
                        UserInfo.tp_Title,
                        Docs.TimeLastModified AS LastModified,
                        Docs.[Size],
                        Docs.Id AS ItemId,
                        Docs#CT.Rank AS CT#Rank,
                        CASE WHEN (Docs.Type = 1)
                             THEN 3
                             ELSE 2
                        END
                  FROM
                        Docs
                  INNER JOIN
                        ContainsTable(Docs, *, @CustSearchTerm) AS Docs#CT
                  ON
                        Docs#CT.[Key] = Docs.ID
                  LEFT OUTER JOIN
                        Lists
                  ON
                        Docs.ListId = Lists.tp_ID
                  LEFT OUTER JOIN
                        UserData
                  ON
                        (Docs.ListId = UserData.tp_ListId) AND
                        (Docs.DocLibRowId = UserData.tp_ID)
                  LEFT OUTER JOIN
                        UserInfo
                  ON
                        (UserData.tp_Editor = UserInfo.tp_ID) AND
                        (UserData.tp_SiteId = UserInfo.tp_SiteID)
                  WHERE
                        @SiteId = Docs.SiteId AND
                        @WebId = Docs.WebId AND
                        ((Lists.tp_Flags IS NULL) OR
                         ((Lists.tp_Flags & 0x400) = 0) OR
                         (UserData.tp_ModerationStatus IS NULL) OR
                         (UserData.tp_ModerationStatus = 0)) AND
                        ((Docs.Type = 0) OR
                         ((Docs.Type = 1) AND
                          (Docs.DoclibRowId IS NOT NULL))) AND
                        ((@RankPaging IS NULL) OR
                         (Docs#CT.Rank < @RankPaging) OR
                         ((Docs#CT.Rank = @RankPaging) AND
                          ((Docs.TimeLastModified < @ModifiedPaging) OR
                           ((Docs.TimeLastModified = @ModifiedPaging) AND
                            (Docs.Id > @ItemIdPaging)))))
                  ORDER BY
                        CT#Rank DESC,
                        LastModified DESC,
                        ItemId ASC
                  RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE proc_FetchListItemSearchResults
(
                  @SearchTerm nvarchar(255),
                  @SiteId uniqueidentifier,
                  @WebId uniqueidentifier,
                  @RankPaging int,
                  @ModifiedPaging datetime,
                  @ItemIdPaging uniqueidentifier)
            AS
            
                  IF CHARINDEX(' OR ', @SearchTerm) > 0 OR CHARINDEX(' AND ', @SearchTerm) > 0
                  Begin
                    EXEC proc_FetchListItemSearchResultsBoolean @SearchTerm, @SiteId, @WebId, @RankPaging, @ModifiedPaging, @ItemIdPaging
                    RETURN
                  End
            
                  SET NOCOUNT ON
                  SELECT
                        UserData.nvarchar1,
                        UserData.nvarchar3,
                        UserData.nvarchar7,
                        Docs.DirName,
                        Docs.LeafName,
                        UserData.tp_ID,
                        UserData.tp_ListId,
                        Lists.tp_Title,
                        UserData.tp_Author,
                        UserData.tp_Editor,
                        UserInfo.tp_Title,
                        UserData.tp_Modified AS LastModified,
                        NULL,
                        UserData.tp_GUID AS ItemId,
                        ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)
                              AS CT#Rank,
                        0
                  FROM
                        UserData
                  LEFT OUTER JOIN
                        FreeTextTable(UserData, *, @SearchTerm) AS UserData#CT
                  ON
                        UserData#CT.[Key] = UserData.tp_GUID
                  LEFT OUTER JOIN
                        Lists
                  ON
                        (Lists.tp_WebId = @WebId) AND
                        (Lists.tp_ID = UserData.tp_ListId)
                  LEFT OUTER JOIN
                        UserInfo
                  ON
                        (UserInfo.tp_ID = UserData.tp_Editor) AND
                        (UserInfo.tp_SiteID = UserData.tp_SiteId)
                  LEFT OUTER JOIN
                        Docs
                  ON
                        (Docs.SiteId = @SiteId) AND
                        (Docs.ListId = UserData.tp_ListId) AND
                        (Docs.DocLibRowId = UserData.tp_ID)
                  LEFT OUTER JOIN
                        FreeTextTable(UserInfo, *, @SearchTerm) AS UserInfo#CT
                  ON
                        UserInfo#CT.[Key] = UserInfo.tp_GUID
                  WHERE
                        (@WebId = Lists.tp_WebId) AND
                        ((Lists.tp_Flags IS NULL) OR
                         ((Lists.tp_Flags & 0x400) = 0) OR
                         (UserData.tp_ModerationStatus = 0)) AND
                        ((UserData#CT.Rank IS NOT NULL) OR
                         (UserInfo#CT.Rank IS NOT NULL)) AND
                        ((@RankPaging IS NULL) OR
                         (ISNULL(UserData#CT.Rank, 0) +
                          ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR
                         ((ISNULL(UserData#CT.Rank, 0) +
                           ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND
                          ((UserData.tp_Modified < @ModifiedPaging) OR
                           ((UserData.tp_Modified = @ModifiedPaging) AND
                            (UserData.tp_GUID > @ItemIdPaging)))))
                  UNION ALL
                  SELECT
                        Lists.tp_Title,
                        NULL,
                        NULL,
                        Docs.DirName,
                        Docs.LeafName,
                        NULL,
                        Lists.tp_ID,
                        NULL,
  NULL,
                     NULL,
                        NULL,
                        Lists.tp_Modified AS LastModified,
                        NULL,
                        Lists.tp_ID AS ItemId,
                        Lists#CT.Rank AS CT#Rank,
                   1
                  FROM
                        Lists
                  INNER JOIN
                        FreeTextTable(Lists, *, @SearchTerm) AS Lists#CT
                  ON
                        Lists#CT.[Key] = Lists.tp_ID
                  LEFT OUTER JOIN
                        Docs
                  ON
                        Lists.tp_RootFolder = Docs.[Id]
                  WHERE
                        (@WebId = Lists.tp_WebId) AND
                        ((@RankPaging IS NULL) OR
                         (Lists#CT.Rank < @RankPaging) OR
                         ((Lists#CT.Rank = @RankPaging) AND
                          ((Lists.tp_Modified < @ModifiedPaging) OR
                           ((Lists.tp_Modified = @ModifiedPaging) AND
                            (Lists.tp_ID > @ItemIdPaging)))))
                  ORDER BY
                        CT#Rank DESC,
                        LastModified DESC,
                        ItemId ASC
                  RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE proc_FetchListItemSearchResultsBoolean
(
                  @SearchTerm nvarchar(255),
                  @SiteId uniqueidentifier,
                  @WebId uniqueidentifier,
                  @RankPaging int,
                  @ModifiedPaging datetime,
                  @ItemIdPaging uniqueidentifier)
            AS
            
                  DECLARE @CustSearchTerm nvarchar(255)
                  SET @CustSearchTerm = '"' + @SearchTerm + '"'
            
                  SET NOCOUNT ON
                  SELECT
                        UserData.nvarchar1,
                        UserData.nvarchar3,
                        UserData.nvarchar7,
                        Docs.DirName,
                        Docs.LeafName,
                        UserData.tp_ID,
                        UserData.tp_ListId,
                        Lists.tp_Title,
                        UserData.tp_Author,
                        UserData.tp_Editor,
                        UserInfo.tp_Title,
                        UserData.tp_Modified AS LastModified,
                        NULL,
                        UserData.tp_GUID AS ItemId,
                        ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)
                              AS CT#Rank,
                        0
                  FROM
                        UserData
                  LEFT OUTER JOIN
                        ContainsTable(UserData, *, @CustSearchTerm) AS UserData#CT
                  ON
                        UserData#CT.[Key] = UserData.tp_GUID
                  LEFT OUTER JOIN
                        Lists
                  ON
                        (Lists.tp_WebId = @WebId) AND
                        (Lists.tp_ID = UserData.tp_ListId)
                  LEFT OUTER JOIN
                        UserInfo
                  ON
                        (UserInfo.tp_ID = UserData.tp_Editor) AND
                        (UserInfo.tp_SiteID = UserData.tp_SiteId)
                  LEFT OUTER JOIN
                        Docs
                  ON
                        (Docs.SiteId = @SiteId) AND
                        (Docs.ListId = UserData.tp_ListId) AND
                        (Docs.DocLibRowId = UserData.tp_ID)
                  LEFT OUTER JOIN
                        ContainsTable(UserInfo, *, @CustSearchTerm) AS UserInfo#CT
                  ON
                        UserInfo#CT.[Key] = UserInfo.tp_GUID
                  WHERE
                        (@WebId = Lists.tp_WebId) AND
                        ((Lists.tp_Flags IS NULL) OR
                         ((Lists.tp_Flags & 0x400) = 0) OR
                         (UserData.tp_ModerationStatus = 0)) AND
                        ((UserData#CT.Rank IS NOT NULL) OR
                         (UserInfo#CT.Rank IS NOT NULL)) AND
                        ((@RankPaging IS NULL) OR
                         (ISNULL(UserData#CT.Rank, 0) +
                          ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR
                         ((ISNULL(UserData#CT.Rank, 0) +
                           ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND
                          ((UserData.tp_Modified < @ModifiedPaging) OR
                           ((UserData.tp_Modified = @ModifiedPaging) AND
                            (UserData.tp_GUID > @ItemIdPaging)))))
                  UNION ALL
                  SELECT
                        Lists.tp_Title,
                        NULL,
                        NULL,
                        Docs.DirName,
                        Docs.LeafName,
                        NULL,
                        Lists.tp_ID,
                        NULL,
  NULL,
                     NULL,
                        NULL,
                        Lists.tp_Modified AS LastModified,
                        NULL,
                        Lists.tp_ID AS ItemId,
                        Lists#CT.Rank AS CT#Rank,
                   1
                  FROM
                        Lists
                  INNER JOIN
                        ContainsTable(Lists, *, @CustSearchTerm) AS Lists#CT
                  ON
                        Lists#CT.[Key] = Lists.tp_ID
                  LEFT OUTER JOIN
                        Docs
                  ON
                        Lists.tp_RootFolder = Docs.[Id]
                  WHERE
                        (@WebId = Lists.tp_WebId) AND
                        ((@RankPaging IS NULL) OR
                         (Lists#CT.Rank < @RankPaging) OR
                         ((Lists#CT.Rank = @RankPaging) AND
                          ((Lists.tp_Modified < @ModifiedPaging) OR
                           ((Lists.tp_Modified = @ModifiedPaging) AND
                            (Lists.tp_ID > @ItemIdPaging)))))
                  ORDER BY
                        CT#Rank DESC,
                        LastModified DESC,
                        ItemId ASC
                  RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 
 
(Unchecked by Editor)

 

Thanks, Billy!
 

 

 

-------------

 

The standard WSS search is very annoying as it contains no boolean searches. The way to fix this is to alter the stored procedure that controls the searching. proc_FetchDocSearchResults controls
searching in documents.

 

The solution I used (found it somewhere, posted by Carl Lewis) consists of 2 stored procedures,

 

First you replace the proc_FetchDocSearchResults with the code below. (Always make backups!)

Story continues below code ->

---------------------------------------------------

CREATE PROCEDURE proc_FetchDocSearchResults(@SearchTerm nvarchar(255),
@SiteId uniqueidentifier, @WebId uniqueidentifier, @RankPaging int,
@ModifiedPaging datetime, @ItemIdPaging uniqueidentifier) AS
DECLARE @i INT, @spos INT, @QuotMode BIT, @tstring VARCHAR(255), @fl
INT

--execute the other procedure if this is a freetext search
IF SubString(@SearchTerm,2,2) = 'FT'  BEGIN
  SET @SearchTerm = '"' + SubString(@SearchTerm, 5,len(@SearchTerm))
  EXECUTE proc_FetchDocSearchResultsFreeText @SearchTerm , @SiteId ,
@WebId , @RankPaging , @ModifiedPaging , @ItemIdPaging
END
ELSE BEGIN

/* Fix the search term */
--Remove any leading and ending quotes and spaces from the Search Term
IF LEFT(@SearchTerm, 1) = CHAR(34) BEGIN
  SET @SearchTerm = RIGHT(@SearchTerm, LEN(@SearchTerm) -1 )
END
IF RIGHT(@SearchTerm, 1) = CHAR(34) BEGIN
  SET @SearchTerm = LEFT(@SearchTerm, LEN(@SearchTerm) - 1)
END
SET @SearchTerm = RTRIM(LTRIM(@SearchTerm))

--Remove ' ', ' or ', ' and ' from beginning and end of query (for
users who don't know what they are doing)
WHILE CHARINDEX('  ', @SearchTerm) > 0 BEGIN
  SET @i = CHARINDEX('  ', @SearchTerm)
  SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @i-1) +
SUBSTRING(@SearchTerm, @i+1, LEN(@SearchTerm))
END

--if the first phrase is "and" or "or", remove it
WHILE RTRIM(LTRIM(SUBSTRING(@SearchTerm,1,4))) = 'and' or
RTRIM(LTRIM(SUBSTRING(@SearchTerm,1,3))) = 'or' BEGIN
  IF RTRIM(LTRIM(SUBSTRING(@SearchTerm,1,4))) = 'and'
    SET @SearchTerm = SUBSTRING(@SearchTerm,4, LEN(@SearchTerm))
  ELSE
    SET @SearchTerm = SUBSTRING(@SearchTerm,3, LEN(@SearchTerm))

  SET @SearchTerm = RTRIM(LTRIM(@SearchTerm))
END

--if the last phrase is "and" or "or", remove it
WHILE RTRIM(LTRIM(SUBSTRING(@SearchTerm, LEN(@SearchTerm)-3,
LEN(@SearchTerm)))) = 'and' or RTRIM(LTRIM(SUBSTRING(@SearchTerm,
LEN(@SearchTerm)-2, LEN(@SearchTerm)))) = 'or' BEGIN
  IF RTRIM(LTRIM(SUBSTRING(@SearchTerm, LEN(@SearchTerm)-3,
LEN(@SearchTerm)))) = 'and'
    SET @SearchTerm = SUBSTRING(@SearchTerm, 1, LEN(@SearchTerm)-4)
  ELSE
    SET @SearchTerm = SUBSTRING(@SearchTerm, 1, LEN(@SearchTerm)-3)
  SET @SearchTerm = RTRIM(LTRIM(@SearchTerm))
END

--add "and" between words that are not phrases (a phrase has "s around
it)
SET @spos = 1
SET @i = 0
WHILE @spos > 0 BEGIN
  --find the next occurance of a space ' ' in the searchstring
  SET @spos = CHARINDEX(' ', @SearchTerm, @spos)
  IF @spos = 0 BREAK -- quit if there are none left

  --check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
  SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
  SET @fl = LEN(@tstring)
  SET @tstring = REPLACE(@tstring, '"', '')
  IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode =
1

  --are we in the middle of a quote? if so don't add the "and"
  IF @QuotMode = 1 BEGIN
    --move the position to the next character
    SET @spos = @spos + 1
  END
  ELSE BEGIN --else add the " and "
    SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + ' AND ' +
SUBSTRING(@SearchTerm, @spos+1, LEN(@SearchTerm))
    SET @spos = @spos + 5 --change the position of the index to after
the new " and "
  END
END

--eliminate ' and or and '
SET @spos = 1
WHILE @spos > 0 BEGIN
  --find the next occurance of a ' and or and ' in the searchstring
  SET @spos = CHARINDEX(' and or and ', @SearchTerm, @spos)
  IF @spos = 0 BREAK -- quit if there are none left

  --check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
  SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
  SET @fl = LEN(@tstring)
  SET @tstring = REPLACE(@tstring, '"', '')
  IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode =
1

  --are we in the middle of a quote? if so don't fix the occurance
  IF @QuotMode = 1 BEGIN
    --move the position to the next character
    SET @spos = @spos + 12
  END
  ELSE BEGIN --else fix it
    SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + ' or ' +
SUBSTRING(@SearchTerm, @spos+12, LEN(@SearchTerm))
    SET @spos = @spos + 4 --change the position of the index to after
the fixed ' or '
  END
END

--eliminate ' and and and '
SET @spos = 1
WHILE @spos > 0 BEGIN
  --find the next occurance of a ' and and and ' in the searchstring
  SET @spos = CHARINDEX(' and and and ', @SearchTerm, @spos)
  IF @spos = 0 BREAK -- quit if there are none left

  --check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
  SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
  SET @fl = LEN(@tstring)
  SET @tstring = REPLACE(@tstring, '"', '')
  IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode =
1

  --are we in the middle of a quote? if so don't fix the occurance
  IF @QuotMode = 1 BEGIN
    --move the position to the next character
    SET @spos = @spos + 13
  END
  ELSE BEGIN --else add the " and "
    SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + ' and ' +
SUBSTRING(@SearchTerm, @spos+13, LEN(@SearchTerm))
    SET @spos = @spos + 5 --change the position of the index to after
the fixed ' and '
  END
END

--eliminate ' and not and '
SET @spos = 1
WHILE @spos > 0 BEGIN
  --find the next occurance of a ' and not and ' in the searchstring
  SET @spos = CHARINDEX(' and not and ', @SearchTerm, @spos)
  IF @spos = 0 BREAK -- quit if there are none left

  --check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
  SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
  SET @fl = LEN(@tstring)
  SET @tstring = REPLACE(@tstring, '"', '')
  IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode =
1

  --are we in the middle of a quote? if so don't fix the occurance
  IF @QuotMode = 1 BEGIN
    --move the position to the next character
    SET @spos = @spos + 13
  END
  ELSE BEGIN --else add the " and "
    SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + ' and not '
+ SUBSTRING(@SearchTerm, @spos+13, LEN(@SearchTerm))
    SET @spos = @spos + 5 --change the position of the index to after the fixed ' and not '
  END
END

--find ** and change the word to FORMSOF(INFLECTIONAL, <word>)
--to do the replace we need to reverse the string and then replace
SET @SearchTerm = REVERSE(@SearchTerm)
SET @spos = 1
WHILE @spos > 0 BEGIN
  --find the next occurance of a '**' in the searchstring
  SET @spos = CHARINDEX('**', @SearchTerm, @spos)
  IF @spos = 0 BREAK -- quit if there are none left

  --check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
  SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
  SET @fl = LEN(@tstring)
  SET @tstring = REPLACE(@tstring, '"', '')
  IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode =
1

  --are we in the middle of a quote? if so don't change the **
  IF @QuotMode = 1 BEGIN
    --move the position to the next character
    SET @spos = @spos + 2
  END
  ELSE BEGIN --else change it
   --find the ' ' before the word
    SET @i = CHARINDEX(' ', @SearchTerm, @spos)
    IF @i = 0 SET @i = LEN(@SearchTerm)+1
    SET @tstring = SUBSTRING(@SearchTerm, @spos+2, @i-@spos-2)
    SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + ')' +
@tstring + reverse('FORMSOF(INFLECTIONAL, ') + SUBSTRING(@SearchTerm,
@spos+LEN(@tstring)+2, LEN(@SearchTerm))
  END
END

--find * and change the word to "<word>*"
--to do the replace we need to reverse the string and then replace;
already reversed remember
SET @spos = 1
WHILE @spos > 0 BEGIN
  --find the next occurance of a '*' in the searchstring
  SET @spos = CHARINDEX('*', @SearchTerm, @spos)
  IF @spos = 0 BREAK -- quit if there are none left

  --check if we are in the middle of a quote: count the number of "s.
if they are uneven, we are in a quote
  SET @tstring = SUBSTRING(@SearchTerm, 1, @spos)
  SET @fl = LEN(@tstring)
  SET @tstring = REPLACE(@tstring, '"', '')
  IF (@fl-LEN(@tstring)) % 2 = 0 SET @QuotMode = 0 ELSE SET @QuotMode
= 1

  --are we in the middle of a quote? if so don't change the *
  IF @QuotMode = 1 BEGIN
    --move the position to the next character
    SET @spos = @spos + 1
  END
  ELSE BEGIN --else change it
   --find the ' ' before the word
    SET @i = CHARINDEX(' ', @SearchTerm, @spos)
    IF @i = 0 SET @i = LEN(@SearchTerm)+1
    SET @tstring = SUBSTRING(@SearchTerm, @spos, @i-1)
    SET @SearchTerm = SUBSTRING(@SearchTerm, 1, @spos-1) + char(34) +
@tstring + char(34) + SUBSTRING(@SearchTerm, @spos+LEN(@tstring),
LEN(@SearchTerm))
  END
END
SET @SearchTerm = REVERSE(@SearchTerm)

/*do the search */

                  SET NOCOUNT ON
                  SELECT
                        NULL,
                        NULL,
                        NULL,
                        Docs.DirName,
                        Docs.LeafName,
                        Docs.DocLibRowId,
                        Docs.ListId,
                        Lists.tp_Title,
                        UserData.tp_Author,
                        UserData.tp_Editor,
                        UserInfo.tp_Title,
                        Docs.TimeLastModified AS LastModified,
                        Docs.[Size],
                        Docs.Id AS ItemId,
                        Docs#CT.Rank AS CT#Rank,
                        CASE WHEN (Docs.Type = 1)
                             THEN 3
                             ELSE 2
                        END
                  FROM
                        Docs
                  INNER JOIN
                        ContainsTable(Docs, *, @SearchTerm) AS Docs#CT
                  ON
                        Docs#CT.[Key] = Docs.ID
                  LEFT OUTER JOIN
                        Lists
                  ON
                        Docs.ListId = Lists.tp_ID
                  LEFT OUTER JOIN
                        UserData
                  ON
                        (Docs.ListId = UserData.tp_ListId) AND
                        (Docs.DocLibRowId = UserData.tp_ID)
                  LEFT OUTER JOIN
                        UserInfo
                  ON
                        (UserData.tp_Editor = UserInfo.tp_ID) AND
                        (UserData.tp_SiteId = UserInfo.tp_SiteID)
                  WHERE
                        @SiteId = Docs.SiteId AND
                        @WebId = Docs.WebId AND
                        ((Lists.tp_Flags IS NULL) OR
                         ((Lists.tp_Flags & 0x400) = 0) OR
                         (UserData.tp_ModerationStatus IS NULL) OR
                         (UserData.tp_ModerationStatus = 0)) AND
                        ((Docs.Type = 0) OR
                         ((Docs.Type = 1) AND
                          (Docs.DoclibRowId IS NOT NULL))) AND
                        ((@RankPaging IS NULL) OR
                         (Docs#CT.Rank < @RankPaging) OR
                         ((Docs#CT.Rank = @RankPaging) AND
                          ((Docs.TimeLastModified < @ModifiedPaging)
    OR
                           ((Docs.TimeLastModified = @ModifiedPaging)
    AND
                            (Docs.Id > @ItemIdPaging)))))
                  ORDER BY
             LeafName ASC,
                        CT#Rank DESC,
                        LastModified DESC,
                        ItemId ASC
                  RETURN 0
END
GO

---------------------------------------------------

 

Then add a new stored procedure named proc_FetchDocSearchResultsFreeText this stored procedure must contain the original procedure found in the proc_FetchDocSearchResults. That
should be the procedure below:

 

-------------------------------------------------------CREATE PROCEDURE proc_FetchDocSearchResultsFreeText(
                  @SearchTerm nvarchar(255),
                  @SiteId uniqueidentifier,
                  @WebId uniqueidentifier,
                  @RankPaging int,
                  @ModifiedPaging datetime,
                  @ItemIdPaging uniqueidentifier)
            AS
                  SET NOCOUNT ON
                  SELECT
                        NULL,
                        NULL,
                        NULL,
                        Docs.DirName,
                        Docs.LeafName,
                        Docs.DocLibRowId,
                        Docs.ListId,
                        Lists.tp_Title,
                        UserData.tp_Author,
                        UserData.tp_Editor,
                        UserInfo.tp_Title,
                        Docs.TimeLastModified AS LastModified,
                        Docs.[Size],
                        Docs.Id AS ItemId,
                        Docs#CT.Rank AS CT#Rank,
                        CASE WHEN (Docs.Type = 1)
                             THEN 3
                             ELSE 2
                        END
                  FROM
                        Docs
                  INNER JOIN
                        FreeTextTable(Docs, *, @SearchTerm) AS Docs#CT
                  ON
                        Docs#CT.[Key] = Docs.ID
                  LEFT OUTER JOIN
                        Lists
                  ON
                        Docs.ListId = Lists.tp_ID
                  LEFT OUTER JOIN
                        UserData
                  ON
                        (Docs.ListId = UserData.tp_ListId) AND
                        (Docs.DocLibRowId = UserData.tp_ID)
                  LEFT OUTER JOIN
                        UserInfo
                  ON
                        (UserData.tp_Editor = UserInfo.tp_ID) AND
                        (UserData.tp_SiteId = UserInfo.tp_SiteID)
                  WHERE
                        @SiteId = Docs.SiteId AND
                        @WebId = Docs.WebId AND
                        ((Lists.tp_Flags IS NULL) OR
                         ((Lists.tp_Flags & 0x400) = 0) OR
                         (UserData.tp_ModerationStatus IS NULL) OR
                         (UserData.tp_ModerationStatus = 0)) AND
                        ((Docs.Type = 0) OR
                         ((Docs.Type = 1) AND
                          (Docs.DoclibRowId IS NOT NULL))) AND
                        ((@RankPaging IS NULL) OR
                         (Docs#CT.Rank < @RankPaging) OR
                         ((Docs#CT.Rank = @RankPaging) AND
                          ((Docs.TimeLastModified < @ModifiedPaging) OR
                           ((Docs.TimeLastModified = @ModifiedPaging)
AND
                            (Docs.Id > @ItemIdPaging)))))
                  ORDER BY
                        LeafName ASC,
                        CT#Rank DESC,
                        LastModified DESC,
                        ItemId ASC
                  RETURN 0
GO

-------------------------------------------------------BTW if you create a new procedure don't assign a name that is contained in the above example it will be created with the right name automatically!

 

When you have done this you can still use the standard search box of WSS, but now all standard searches are preformed with the AND operator
between the search words, and it searches for exact matches no more inflextual searches.

 

You can however use the orginal search by typing FT for your query..

 

You can add Operators between words like "OR", "AND", "AND NOT" and you can put "" around your query.

 

You can also end your query words with a * or **.

 

* means that if you search for apple you will also find appletree, applepie.

 

The use of ** means that inflectual searching is applied for that word meaning it searches for variations of example, swim as swimming swam but it only does so in english by default.

 

For something extra I found MHT files to be perfect for WSS they look good as webpages and contain the pictures in them, besides that they are edited in Word!

 

But MHT's are ignored from search this is a simple solution to add them to the search:

 

To search mht files you need to add mht files to the indexing service.

 

1. Log on to the SQL server that Windows SharePoint Services uses as its database server.

 

2. Click Start, click Run, type regedit, and then click OK.

 

3. Locate, and then click the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\Filters\Extension

 

4. Right-click the Extension registry key, point to New, and then click Key.

 

5. Type .mht to name the new key.

 

Note There is a period (.) in front of "mht".

 

6. Click the .mht registry key.

 

7. In the right pane, right-click (Default), and then click Modify.

 

8. In the Value data box, type the following line, and then click OK:

 

{5645C8C2-E277-11CF-8FDA-00AA00A14F93}

 

Note This registry value contains zeros, not the letter O.

 

9. Quit Registry Editor, and then restart the Microsoft Search service (MSSearch).

 

To restart this service:

a.  Click Start, click Run, type services.msc
in the Open box, and then click OK.
b.  In the Name list, click Microsoft Search, and then click the Restart link.

 

remark 1: if you want more functionalty I would suggest writing your own search webpart, I did this so I could specify the scope of the search.

 

You can use the SPWeb.SearchDocuments(<Search Input Here!>) and the SPWeb.SearchListItems(<Search Input Here!>).

 

remark 2: the above stored procedures only affect document searching not the search on list items but you could implement something
similar yourself!

(Unchecked by Editor and I was assured by Paul that the code isn't the same as that provided by Billy)

 

 

The answer is that the Alert picks up on the way the site was accessed when the Alert was first created.

 

If you access the site using the URL and then create an Alert, the Alert will include the URL in its message to you.

 

If you access the site using the sitename and then create an Alert, the Alert will include the sitename in its message to you.

 

The fact that this happens is included in this KB article

which doesn't as you will see give a solution.

 

 

 

By default the site TCP port is 80 and the SSL port is 443. Change the TCP port to a value other than 80 (I used 8088)

 

 

 

 

Todd also provides images for each step at

 

 

 

 

respectively.

 

 

 

Another way to do this (without needing to make another virtual server) is to make use of a Custom Error page for the site in IIS:

 

On the server, in Windows Explorer, open the

 

C:\Windows\Help\iisHelp\common folder

 

Holding the Control key, drag the 403-4.htm file and drop to create a copy

 

Rename the new copy my403-4.htm

 

Right click my403-4.htm and Open With... NotePad

 

Paste the following script code into the page, just after the <HEAD> tag:

    <SCRIPT LANGUAGE="JavaScript">
    if (location.protocol != 'https:'){
         window.location= 'https://' + location.host + location.pathname + location.search
    }
    </SCRIPT>

 

Save your changes and close the file

 

In IIS, right click your web site, go to properties

 

On the Custom Errors tab, highlight the entry for 403;4 and click the Edit button

 

Change the File entry to read

 

C:\WINDOWS\Help\iisHelp\common\my403-4.htm

 

or Browse to the file

 

Click OK to dismiss the dialog and again to close the Properties dialog
 
Try to visit any page on the site using http instead of https and you will see the address automagically rewritten to https.

 

 

Vincent Koopman commented
 
I had a redirect to overcome the http-https problem.

 

However, the redirect URL in IIS was set to go to https://my.domain.com. So requests for HTTP://my.domain.com/sites/asite were redirected to HTTPS://my.domain.com. So every HTTP request was redirected to the portal homepage. To make IIS redirect to the complete URL I added $V to the redirect URL. SO my redirect URL was HTTPS://my.domain.com$V. $V is a redirect variable and the way I found it in the help of IIS6 was to click on the help button in the Home Directory tab of the properties screen of the website, scroll down and follow the link at the bottom.  

 

 

 

John Lehew gives us some additional code

 

This adds www. if missing.  Certs are only good for an exact web address and many of my users do not type in the www. anymore.

<SCRIPT type=text/javascript>
<!--
if (location.protocol != 'https:')
{
    if (location.host.substring(0,4) == 'www.')
    {
window.location = 'https://'+ location.host + location.pathname + location.search;
    }
    else
    {
window.location = 'https://www.'+ location.host + location.pathname + location.search;
    }
}
// -->
</SCRIPT>

 

 

 

If you want to read to full text please go there and while you are there read his other SharePoint blog items. Here is a brief re-written extract of the salient points.

 

Notifications are sent when the index crawls so if you want notifications to be sent out during the day, you need to set the index to crawl during the day (not usually a good idea).

 

Theoretically (according to the documentation) you should be able to specify when alerts are sent out by using the (WSS) job-daily-alerts, job-weekly-alerts, and job-immediate-alerts properties of STSADM.exe but these don't (according to PSS) work.

 

[The equivalent properties for SPS 003 are job-daily-notification, job-weekly-notification, and job-immediate-notification just to confuse us - they don't work there either.]