如何使用Windows搜索服务和SQLServer 2008 R2进行全文搜索？内容来源于 Stack Overflow，并遵循CC BY-SA 3.0许可协议进行翻译与使用
内容来源于 Stack Overflow，并遵循CC BY-SA 3.0许可协议进行翻译与使用
- 回答 (1)
- 关注 (0)
- 查看 (164)
目前，我正在尝试从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
扫码关注云+社区