内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用
目前,我正在尝试从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库吗?
我知道在过去的几年里,越来越多的人一直在努力解决这个问题。我想知道是否有人能把它启动并运行,或者能给我指明正确的方向。
普通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