如何使用Windows搜索服务和SQLServer 2008 R2进行全文搜索?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (99)

目前,我正在尝试从SQLServer2008R2实例(也在SQLServer 2012上进行了测试)查询Windows搜索服务。Windows搜索被公开为OLE DB数据源,为我提供了几个查询搜索索引的选项。在SQLServer中配置新的链接服务器时,ManagementStudio允许我选择MicrosoftOLE DB提供程序进行搜索,这意味着我应该能够从SQLServer连接到它。然而,要让它启动并运行起来却是一个挑战。下面你会发现我偶然发现的错误信息。

OLE DB provider "Search.CollatorDSO" for linked server "TESTSERVER" returned message "Command was not prepared.".

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Search.CollatorDSO" for linked server "TESTSERVER" reported an error. Command was not prepared.

Msg 7350, Level 16, State 2, Line 2

Cannot get the column information from OLE DB provider "Search.CollatorDSO" for linked server "TESTSERVER".

,我需要搜索网络驱动器,这能使用共享Windows库吗?

我知道在过去的几年里,越来越多的人一直在努力解决这个问题。我想知道是否有人能把它启动并运行,或者能给我指明正确的方向。

OLEDB工程

普通ADO/OLEDB组件可以使用连接字符串查询Windows搜索服务:

provider=Search.CollatorDSO.1;EXTENDED PROPERTIES="Application=Windows"

还有一个示例查询:

SELECT TOP 100000 "System.ItemName",
    "System.ItemNameDisplay",
    "System.ItemType",
    "System.ItemTypeText",
    "System.Search.EntryID",
    "System.Search.GatherTime",
    "System.Search.HitCount",
    "System.Search.Store",
    "System.ItemUrl",
    "System.Filename",
    "System.FileExtension",
    "System.ItemFolderPathDisplay",
    "System.ItemPathDisplay",
    "System.DateModified",
    "System.ContentType",
    "System.ApplicationName",
    "System.KindText",
    "System.ParsingName",
    "System.SFGAOFlags",
    "System.Size",
    "System.ThumbnailCacheId"
FROM "SystemIndex"
WHERE CONTAINS(*,'"Contoso*"',1033)

您可以尝试运行以下命令,在SQLServer管理Studio中直接在SQLServer上尝试查询:

SELECT *
FROM OPENROWSET(
        'Search.CollatorDSO', 
        'Application=Windows', 
        'SELECT TOP 100 "System.ItemName", "System.FileName" FROM SystemIndex');

这就产生了错误:

OLE DB provider "Search.CollatorDSO" for linked server "(null)" returned message "Command was not prepared.".  

Msg 7399, Level 16, State 1, Line 1  
The OLE DB provider "Search.CollatorDSO" for linked server "(null)" reported an error. Command was not prepared.  
Msg 7350, Level 16, State 2, Line 1  
Cannot get the column information from OLE DB provider "Search.CollatorDSO" for linked server "(null)".  

提问于
用户回答回答于

USE [YourDB]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    Create PROC [dbo].[SearchAllTables]
    @SearchStr nvarchar(100)
    AS
    BEGIN
    DECLARE @dml nvarchar(max) = N''        
    IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE dbo.#Results
    CREATE TABLE dbo.#Results
     ([tablename] nvarchar(100), 
      [ColumnName] nvarchar(100), 
      [Value] nvarchar(max))  
    SELECT @dml += ' SELECT ''' + s.name + '.' + t.name + ''' AS [tablename], ''' + 
                    c.name + ''' AS [ColumnName], CAST(' + QUOTENAME(c.name) + 
                   ' AS nvarchar(max)) AS [Value] FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) +
                   ' (NOLOCK) WHERE CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) LIKE ' + '''%' + @SearchStr + '%'''
    FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id
                       JOIN sys.columns c ON t.object_id = c.object_id
                       JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c .user_type_id = ty .user_type_id
    WHERE t.is_ms_shipped = 0 AND ty.name NOT IN ('timestamp', 'image', 'sql_variant')

    INSERT dbo.#Results
    EXEC sp_executesql @dml

    SELECT *
    FROM dbo.#Results
    END

扫码关注云+社区