Thank this author by sharing:
By Jon Winer,
Index Server is a very useful tool for querying text within documents. Index Server has
its own query form that may be used from within the application, but if you need to integrate
Index Server functionality into a web application or a 'back-end' process, you will obviously
need to use it programatically. I had difficulties locating a complete source on 'How To Program
with Index Server'. So, hopefully here, I can offer a slightly more well rounded resource than
what I found.
My first venture into Index Server was a bit difficult. The SQL syntax for Index Server
is slightly different than T-SQL, and the objects' properties and methods are not necessarily
intuitive. One significant problem I ran into were the differences between the NT Option Pack
4.0 Index Server object (ixsso.dll) and the Windows 2000 Index Server object (Cisso.dll). Not
only are the names different (which throws off your declarations), but some of the defaults are
different as well. The major difference between the two versions is the Dialect property. In
ixsso.dll, the dialect property defaults to 'free text' mode (1). In Cisso.dll, the default
dialect is 'phrase mode' (2). Knowing this can save hours in debugging time.
Two objects are required to program with Index Server, the Query object and the Utility
object. In the larger example below, I use the Utility object to specify the scope of the query.
The scope is the physical location of the documents. This location should be identical to the
directory path listed on Index Server for a specific catalog. I had better luck accessing the
catalog when the scope referenced a share to the scope location and not the actual path. Once
the scope is assigned to the query object, the Utiliy object may be destroyed. The Query
object is more robust. In my example, I use the query object for defining custom columns,
assigning the query restriction, choosing the columns to be returned, opening the recordset and
setting the dialect property.
The query method I use in my example is not the sole method for querying Index Server. You
may use the more T-SQL like approach:
Select Filename, Size from scope('shallow traversal of "d:\reports"') where contains
(contents, '"accounting"') > 0
When using this syntax, the scope may be left empty (this will set the scope to the default)
or, as in the example above, you may pass arguments to the scope which dictate the 'universe'
of the query. The options are 'shallow traversal of' and 'deep traversal of'. 'Shallow' specifies
that only the directory listed in the scope argument will be queried. 'Deep' infers that the
specified directory and its children will be included in the query. I have not used this method,
but in the examples I have found, this method is mostly used with the ado recordset object. Here
is some sample code that may help:
Dim rs as new adodb.recordset
Dim CmdText as string
CmdText = "select filename from scope('" & chr(34) & scope_arguments & chr(34 & "' where " & _
query_restriction & " order by " & sort_text
rs.open CmdText, "provider=msidxs", adOpenForwardOnly
For more examples of how to use Index Server and other specifics, MSDN Online and the Windows
Help files are a good resource. To view my other articles, feel free to visit my homepage.
Dim RS As ADODB.Recordset
Dim vRestriction As String
'Index Server objects
Dim Qry As Cisso.CissoQuery
Dim Utl As Cisso.CissoUtil
Set Qry = New Cisso.CissoQuery
Set Utl = New Cisso.CissoUtil
'Adds scope to query - physical location of files
Utl.AddScopeToQuery Qry, Scope
Set Utl = Nothing
'Set dialect of query object
.Dialect = 1
'Definition of custom properties in Index Server.
.DefineColumn "pState (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pState"
.DefineColumn "pUpdtYMD (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pUpdtYMD"
.DefineColumn "pCountry (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pCountry"
.DefineColumn "pAppID (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pAppID"
.DefineColumn "pSrcGrp (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pSrcGrp"
.DefineColumn "pCrType (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pCrType"
.DefineColumn "pYrsExp (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pYrsExp"
.DefineColumn "pFullTime (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pFullTime"
.DefineColumn "pSalary (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pSalary"
.DefineColumn "pDegree (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pDegree"
.DefineColumn "pCitizenShip (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pCitizenShip"
.DefineColumn "pRgnIDRsdnt (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pRgnIDRsdnt"
.DefineColumn "pRgnIDPrfSt (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pRgnIDPrfSt"
.DefineColumn "pChannelID (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pChannelID"
'colums to be returned by query
.Columns = "filename, vpath, pState, pUpdtYMD, pCountry, pAppID, pSrcGrp"
'An example of a query string for Index Server
vRestriction = "(@pSrcGrp WB) & (@pUpdtYMD >'2000-10-04 00:00:01') " & _
"(@Contents ((unix) or (systems administration) or " & _
"(unix systems administrator)))"
'Assign query to Query property
.Query = vRestriction
'Location of catalog/index files
.Catalog = Catalog
'Returns query results in desired sort order - This is ordering by the
'pUpdtYMD column in desc order
.SortBy = "pUpdtYMD[d]"
'opens recordset - may also use 'nonsequential' as a cursor type option
Set RS = .CreateRecordset("Sequential")
Do Until .EOF
'loop through recordset
Set RS = Nothing
Set Qry = Nothing
Determining how, and if, SQL Server is using your indexes.
Index fragmentation can adversely affect query response time. When accessing data through an index, ...
Transfer SQL Server Objects Task
This article contains an introduction to SQL Server indexes and how they are used in your queries.
SQL Server Index
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.