Freigeben über

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

  • Artikel

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: https://msdn.microsoft.com/en-us/library/bb266517(VS.85).aspx

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

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

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

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

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

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

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

Sandeep Dasam

SQL Server Support Engineer

Reviewed by,

Nickson Dickson

Tech Lead – Microsoft SQL Server

Comments

  • Anonymous
    December 16, 2009
    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