Connect to Windows Search from SQL Server using Linked Server RRS feed

  • Question

  • I have posted this over in the SQL forum, but this might be the same basic problem if trying to connect to Microsoft Search Server as Windows Search (or Windows Desktop Search), which may really be a better solution for my needs longterm anyway.

    I am trying to create a linked server that receives data from Windows Search 4 (or Windows Desktop Search) so I can associate filename and path information from my files with my metadata in SQL Server 2005. Windows Search is one of the valid providers that I can choose from SQL Server. I can link to Windows Search using VBScript using a simple script such as this:


    'To run this snippet, save it to a file and run it using cscript.exe from a command line. 

    'Running the .vbs file with Windows Script Host may cause dialog boxes to open for each item returned from the index.

    On Error Resume Next

    Set objConnection = CreateObject("ADODB.Connection")

    Set objRecordSet = CreateObject("ADODB.Recordset")

    objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"

    objRecordSet.Open "select system.itempathdisplay from systemindex", objConnection


    Do Until objRecordset.EOF

    Wscript.Echo objRecordset.Fields.Item("System.ItemPathDisplay")




    This provides path information (in DOS) to all of my files in the index as follows:






    What I would like to do is get the same info from within SQL Server 2005 (or similar) using a linked server. In the past I have done this using Indexing Service, but that is an antiquated solution, and it seems that I should be able to link to Windows Search in a similar way.

    I can run the following query in SQL Server (and the connection test is passed):


    SELECT * FROM OPENQUERY("Windows Search3", 'SELECT prop:System.itempathdisplay FROM SYSTEMINDEX') 


    Where Windows Search3 is the name of my linked server. This will return the following error:


    OLE DB provider "Search.CollatorDSO" for linked server "Windows Search3" returned message "One or more errors occurred during processing of command.".

    Msg 7321, Level 16, State 2, Line 1

    An error occurred while preparing the query "SELECT prop:System.itempathdisplay FROM system.search.store:file" for execution against OLE DB provider "Search.CollatorDSO" for linked server "Windows Search3".


    This error seems a little further ahead (or at least different) than the error that other web posters have stated in the past:


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

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Search.CollatorDSO" for linked server "WinSearch4" 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 "WinSearch4".


    Which is the result of:


    SELECT * FROM OPENQUERY("Windows Search3", 'SELECT System.itempathdisplay FROM SYSTEMINDEX') 


    You will notice the only difference is     prop:     before system.itempathdisplay. I think my sql is quite close to what it should be to work, but it seems that something is missing. I think this connects somehow to one of the options in this API http://msdn.microsoft.com/en-us/library/ff684394%28v=vs.85%29.aspx , but it must be lacking something. I don't know enough of how SQL Server repackages this info and sends it off to Windows Search, so I am now clueless. Either a SQL guru or a Windows Search master might be able to jump the last hurdle to make this thing work. It seems like there is only a small piece of the puzzle missing.

    I will dump my linked server info for reference:


    /****** Object: LinkedServer [Windows Search3] Script Date: 01/31/2011 17:23:36 ******/

    EXEC master.dbo.sp_addlinkedserver @server = N'Windows Search3', @srvproduct=N'Microsoft OLE DB Provider for Search', @provider=N'Search.CollatorDSO', @datasrc=N'SYSTEMINDEX', @provstr=N'Application=Windows'

    /* For security reasons the linked server remote logins password is changed with ######## */

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Windows Search3',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'collation compatible', @optvalue=N'true'


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'data access', @optvalue=N'true'


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'dist', @optvalue=N'false'


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'pub', @optvalue=N'false'


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'rpc', @optvalue=N'true'


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'rpc out', @optvalue=N'true'


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'sub', @optvalue=N'false'


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'connect timeout', @optvalue=N'0'


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'collation name', @optvalue=null


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'lazy schema validation', @optvalue=N'false'


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'query timeout', @optvalue=N'0'


    EXEC master.dbo.sp_serveroption @server=N'Windows Search3', @optname=N'use remote collation', @optvalue=N'true'


    My only other thought is that I am having an authentication or impersonation issue, but I am using the security setting (as quoted on the Linked Server Properties page for Security):

    "Be made using the login's current security context"



    I know many people have been looking for this solution, and it seems that it should have been made available sooner. Any help or solution or even agreement appreciated.


    Monday, January 31, 2011 11:03 PM