Query to the SYSTEMINDEX to read the Microsoft search results fails when using Search.CollatorDSO provider

Query to the SYSTEMINDEX to read the Microsoft search results fails when using Search.CollatorDSO provider

  • Comments 1

We can query the results from the Microsoft search through SQL server. If we create a linked server with Search.CollatorDSO provider and try to query the SYSTEMINDEX to read the Microsoft search results. We get an error in SQL server 2008


Below is the query we tried to query the SYTEMINDEX.  Here the linked server name is 'TESTSEARCH'


select filename from OPENQUERY(TESTSEARCH, 'SELECT system. title FROM SYSTEMINDEX')



The Query fails with the following error.


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

Msg 7399, Level 16, State 1, Line 1

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



We cannot always directly return the output from the Sytemindex as the resultset into the SQL server


We can use the following C# code to return the String of top 5 item path from the SYSTEMINDEX data source to the SQL server. the Code establishes a connection to the seach.CollatorDSO and concatanates the output into a single strnig and resturn to the frontend.


                using System.Data.OleDb;

                using System.Data.SqlClient;

                using Microsoft.SqlServer.Server;


                public class cls_searchresults 



                public static void fnc_searchresults()


                SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");


                using(OleDbConnection connection = new OleDbConnection("Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"))



                OleDbCommand cmd = new OleDbCommand("SELECT Top 5 System.ItemPathDisplay FROM SYSTEMINDEX", connection);

                OleDbDataReader reader = cmd.ExecuteReader();

                string strOutput = "";

                while (reader.Read())


                strOutput += reader[0].ToString() + " ";








Then compile the code from the command prompt as a DLL using the following systax.

                csc.exe /target:library result.cs


This create a DLL file with the name result.dll


Change the database property to trustworthy




We can use the CLR feature in SQL server to call this DLL. connect to the SQL server to appropriate database and create the assembly for the DLL file using the below syntax


            CREATE ASSEMBLY result_assembly from 'E:\Cases\CLR\result.dll' WITH PERMISSION_SET = UNSAFE


Create a stored procedure with this assembly as below.




EXTERNAL NAME result_assembly.cls_searchresults.fnc_searchresults()

Execute the stored procedure


            EXEC sql_resul


We get the result in the string concatinated format as below


            Hello world! It's now 5/22/2009 6:39:30 PM


            c:\documents and settings c:\documents and settings\Administrator c:\documents and settings\All Users c:\documents and settings\ASPNET c:\documents and settings\sample


We can try to change the C# code further to obtain the output as a result set to the SQL server.



Querying the Index Programmatically: http://msdn.microsoft.com/en-us/library/bb266517(VS.85).aspx

Overview of the Windows Search SQL Syntax: http://msdn.microsoft.com/en-us/library/bb231255(VS.85).aspx

Using CLR Integration in SQL Server 2005  : http://msdn.microsoft.com/en-us/library/ms345136.aspx

SQL Server 2005: CLR Integration   : http://blogs.msdn.com/sqlclr/

How to: Create and Run a CLR SQL Server User-Defined Function : http://msdn.microsoft.com/en-us/library/w2kae45k(VS.80).aspx

Introduction to SQL Server CLR Integration : http://msdn.microsoft.com/en-us/library/ms254498(VS.80).aspx

Memory Usage in SQL CLR   : http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx

SQL CLR Database Debugging         : http://msdn.microsoft.com/en-us/library/ms165050.aspx




Sandeep Dasam

SQL Server Support Engineer


Reviewed by,

Nickson Dickson

Tech Lead – Microsoft SQL Server

Leave a Comment
  • Please add 8 and 7 and type the answer here:
  • Post
  • your stroed procedure concept  was not clearly anduse full for me.so that syntax for stored procedure will be disply for our website.

    thank you



Page 1 of 1 (1 items)