Linker IT Software
Order Home
menubar-top-links menubar-top-rechts

SQL*XL: Database to Excel bridge

Related LIT software: litLIB: Excel power functions pack encOffice: Protect your Excel file easy and safe

Home Products SQL*XL Help Features Query

Buy now

Download now

SQL*XL online help

 

SQL*XL: Excel and Indexing Service query

SQL*XL: Excel and Indexing Service query

SQL*XL
home

The indexing service makes available information about files through SQL. The OLE DB provider for the indexing service is not providing a few features which makes it a little difficult. After researching the Indexing Service we found out how to use it effectively.

There are 2 features that the Indexing service does not allow and that make life a little difficult. The syntax Select * from table is not supported. That is a pitty because now we have to type out all the column names (find a list of them below). Furthermore it does not seem to support a table listing feature. There is no way to find out which tables are availalbe for querying. Due to this SQL*XL's Describe feature does not display any data.

Table Name:
The table name to use is called scope()

Example queries:

select filename from scope();

select path,vpath from scope();

select filename, size, doctitle from scope();

List of available columns:

Friendly name

Description

Access Last time the document was accessed
AllocSize Size of disk allocation for document
Attrib Document attributes. See the Windows 2000 Platform Software Development Kit.
ClassId Class ID (such as Microsoft Word) of document object
Characterization Characterization, or abstract, of document. This is generated by Indexing Service.
Contents Main contents of document. This can be queried but not retrieved.
Created The time the document was created
Directory Physical path to the document, not including the document name
DocAppName Name of application that created the document
DocAuthor Author of document
DocByteCount Number of bytes in document
DocCategory Type of document (such as a memo, schedule, or white paper)
DocCharCount Number of characters in document
DocComments Comments about the document
DocCompany Name of the company for which the document was written
DocCreatedTm Time document was created
DocEditTime Total time spent editing document
DocHiddenCount Number of hidden slides in a Microsoft PowerPoint document
DocKeywords Document keywords
DocLastAuthor Most recent user who edited document
DocLastPrinted Time document was last printed
DocLastSavedTm Time document was last saved
DocLineCount Number of lines contained in a document
DocManager Name of the manager of the document's author
DocNoteCount Number of pages with notes in a Microsoft PowerPoint document
DocPageCount Number of pages in document
DocParaCount Number of paragraphs in a document
DocPartTitles Names of document parts, such as spreadsheet names in a Microsoft Excel document or slide titles in a Microsoft PowerPoint slide show.
DocPresentationTarget Target format (such as 35mm, printer, video) for a presentation in Microsoft PowerPoint
DocRevNumber Current version number of document
DocSlideCount Number of slides in a Microsoft PowerPoint document
DocSubject Subject of document
DocTemplate Name of template for document
DocTitle Title of document
DocWordCount Number of words in document
FileIndex Unique ID of document
FileName Name of document
HitCount Number of hits (elements in the results list) in document
HtmlHRef Text of HTML link (HREF). This can be queried but not retrieved.
HtmlHeading1 to 6 Text of HTML document in style H1 to H6. This can be queried but not retrieved.
MediaEditor Name of the document's principal editor
MediaOwner Owner of the document, such as the group that licensed the document
MediaProduction Date the media was produced
MediaProject Project to which the document's content belongs
MediaRating Rating of quality or content
MediaSequence_No Sequence number of the media document in the group
MediaSource Name of the document's source, such as the photographer
MediaStatus Status of the document in the project work flow, where 0=Draft, 1=Edit, 2=Final, 3=In Progress, 4=New, 5=Normal, 6=Other, 7=Preliminary, 8=Proof and 9=Review.
MediaSupplier Name of the source of the document, such as the party from which the document was licensed
Path Full physical path to document, including document name
Rank Rank of how well an item in a result list matches query criteria. The range is from 0 to 1,000. Larger numbers indicate better matches.
RankVector Ranks of individual components of a vector query. The range is from 0 to 1,000. Larger numbers indicate better matches.
ShortFileName Short (8.3 format) document name.
Size Size of document, in bytes.
VPath Full virtual path to document, including document name. If there is more than one possible path, the best match for the specific query is chosen.
WorkId Internal ID for document. This is used within Indexing Service.
Write Last time document was modified (written)

This information was found in an indexing service help file located in c:\winnt\help\isconcepts.chm. See topic: Indexing Serive | Advanced Topics | Indexing Service Query Language

See also:

SQL*XL ribbon in Excel