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”
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”).
Thanks, Derek! (and thanks too to Carl for starting things moving.
----------------
Billy Brackeen sent me what he described as an enhanced solution using your method.
Note however the next section (a long way down) which has what the writer of that describes as an improved version of this.
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 following "improved" version of the above was supplied to me by Paul (of The Netherlands) but the main work had already been done by Carl Lewis when he was taking a break from the long jump (kidding - it was another Carl Lewis).
[Note that it's my fault if the new lines are in the wrong place - it was just too much text to edit carefully for this.]
-------------
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
