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



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’)


 


ERROR MESSAGE:


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”.


 



CAUSE:


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


 


RESOLUTION:
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 


                {


                [Microsoft.SqlServer.Server.SqlProcedure]


                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’;”))


                {


                connection.Open();


                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() + ” “;


                }


 


                SqlContext.Pipe.Send(strOutput);


                }


                }


                }


 


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


 


            ALTER DATABASE sample SET TRUSTWORTHY ON


 


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.


 


CREATE PROCEDURE sql_result


                AS


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.


 


REFERENCES :


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

Comments (1)

  1. karthik.e says:

    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

    karthik.e

    indiankathik@hotmail.com