Help,

till i get to my question, some explanation of problem first:

i am searching for fulltext expression in "Fulltext" field of "EntitaFullText" table (with full-text index created) and also in files which "belong to these records", using Indexing Service (http://windowssdk.msdn.microsoft.com/en-us/library/ms692199.aspx) which are stored on hdd.

There are tables:

Entita (IdEntita, ...)
EntitaFullText(IdEntita,FullText, ...)
GaleriaDokumenty(IdGaleriaDokumenty, IdEntita,...)
GaleriaDokument(IdGaleriaDokumenty,IdDokument,...)
Dokument(IdDokument, IdSubor, ... )
Subor(IdSubor, Url, ...)

i have overrriden PredicateExpression.ToQueryText to modify resulting select statement here is source

    public class SearchDocPredicate : PredicateExpression 
    {
        private object[] args;
        private string _idSuborFieldAlias = SuborFieldIndex.IdSubor.ToString();

        public string IdSuborFieldAlias 
        {
            get 
            {
                return _idSuborFieldAlias;
            }
        }

        public SearchDocPredicate(string searchText, JoinHint joinHint ): base() 
        {
            // some predicate, to make sure ToQueryText will be called
            // can be any predicate, it will not be used, because ToQueryText is overriden
            this.Add( PredicateFactory.CompareValue(EntitaFieldIndex.IdEntita,ComparisonOperator.Equal,1));
            
            this.args = new object[] 
            {
                /*  0 */EntityType.GaleriaDokumentyEntity.ToString().Replace("Entity",string.Empty),
                /*  1 */GaleriaDokumentyFieldIndex.IdGaleriaDokumenty.ToString(),
                /*  2 */EntitaFieldIndex.IdEntita.ToString(),
                /*  3 */EntityType.GaleriaDokumentEntity.ToString().Replace("Entity",string.Empty), 
                /*  4 */EntityType.DokumentEntity.ToString().Replace("Entity",string.Empty), 
                /*  5 */EntityType.SuborEntity.ToString().Replace("Entity",string.Empty), 
                /*  6 */DokumentFieldIndex.IdDokument.ToString(), 
                /*  7 */_idSuborFieldAlias, // idSubor field Alias
                /*  8 */SuborFieldIndex.Url.ToString(),
                /*  9 */System.Configuration.ConfigurationSettings.AppSettings["LinkedServerName"],
                /* 10 */System.Configuration.ConfigurationSettings.AppSettings["IndexingServiceServer"],
                /* 11 */System.Configuration.ConfigurationSettings.AppSettings["IndexingServiceCatalog"], 
                /* 12 */((System.Configuration.ConfigurationSettings.AppSettings["RemovePathPrefix"] == null || System.Configuration.ConfigurationSettings.AppSettings["RemovePathPrefix"] == string.Empty)?"replace(oq.path),'\\','/')":string.Format("replace(replace(oq.path,'{0}','doc'),'\\','/')", System.Configuration.ConfigurationSettings.AppSettings["RemovePathPrefix"])), 
                /* 13 */((searchText ==null || searchText == string.Empty)?string.Empty:string.Format(" where contains(''{0}'')",searchText)),
                /* 14 */joinHint.ToString().ToUpper()
            };
        }

        public override string ToQueryText(ref int uniqueMarker)
        {
            return this.ToQueryText(ref uniqueMarker, false);
        }

                public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
        {
            return string.Format(" 1=1"+
                "{14} JOIN "+
                "(select {0}.{2},{5}.{7} from "+
                "{0} INNER JOIN {3} on {0}.{1} = {3}.{1} "+
                "INNER JOIN {4} on {4}.{6} = {3}.{6} "+
                "INNER JOIN {5} on {5}.{7} = {4}.{7} "+
                "INNER JOIN (select {12} as {8} from  "+
                "OPENQUERY({9},'SELECT path FROM \"{10}\".\"{11}\"..SCOPE(){13}') "+
                "as oq) q on q.{8} = {5}.{8}) as {5} "+
                "on {0}.{2} = {5}.{2}", args);
        }
    }

i used it like this:

    SearchDocPredicate docFilter = null;
            bool doc = false;
            JoinHint docSearchType = JoinHint.None;
            if (searchType > 0) 
            {
                doc = true;
                if (searchType == 1)
                    docSearchType = JoinHint.Inner;
                else 
                    docSearchType = JoinHint.Left;
                docFilter = new SearchDocPredicate(fulltextExpresion, docSearchType);
                dynamicList.Relations.Add( EntitaEntity.Relations.GaleriaDokumentyEntityUsingIdEntita , docSearchType).CustomFilter = docFilter;

            }

that dynamicList above is instance of

 EntitaShortListDataTable  : DataTable { 
...
public RelationCollection Relations { get {..} set {...} }
... 

public object getMulti(....) {
...
     dao.GetMultiAsDataTable(resultSetFields, this, 0, sortExpression, predicate, relations, false, groupByCollection, null, pageNumber, pageSize);
...
}

}; 

when i call dynamicList.getMulti(...) "it results" in the select statement like this

...
LEFT JOIN [dbo].[GaleriaDokumenty] ON [dbo].[Entita].[IdEntita]=[dbo].[GaleriaDokumenty].[IdEntita] and 1=1
LEFT JOIN 
(
    select GaleriaDokumenty.IdEntita,Subor.IdSubor from GaleriaDokumenty
    INNER JOIN GaleriaDokument on GaleriaDokumenty.IdGaleriaDokumenty = GaleriaDokument.IdGaleriaDokumenty 
    INNER JOIN Dokument on Dokument.IdDokument = GaleriaDokument.IdDokument INNER JOIN Subor on Subor.IdSubor = Dokument.IdSubor 
    INNER JOIN (select replace(replace(oq.path,'alias','doc'),'\','/') as Url from  
    OPENQUERY(FileSystem,'SELECT path FROM "localhost"."IndexCatalog"..SCOPE() where contains(''"test*" '')') as oq) q on q.Url = Subor.Url
) as Subor 

ON [dbo].[GaleriaDokumenty].[IdEntita] = Subor.IdEntita) 

LEFT JOIN [dbo].[EntitaFullText]  ON  [dbo].[Entita].[IdEntita]=[dbo].[EntitaFullText].[IdEntita] AND ( Contains([dbo].[EntitaFullText].[FullText], @FullText2424)
...
where (IdSubor is not null OR [dbo].[EntitaFullText].[IdEntitaFullText] IS NOT NULL)  
...
@FullText2424 = '"test*"'
...

FileSystem - linked server to indexing service

everinthig works fine, when only 1 user is browsing "web pages" and searching (using code above), but if more users are browsing, some of them are Administrators and they can "manage web page (cms)" and there may be used transactions for some operations, and here comes a problem:

when someone is searching following error occurs:

The requested operation could not be performed because the OLE DB provider 'MSIDXS' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'MSIDXS' IUnknown::QueryInterface returned 0x80004002].

when i make new connection (*1) to database, and execute query

select Q.path from openquery(filesystem,'select path from "localhost"."IndexCatalog"..SCOPE() ') AS Q

it works, but when i just set default transaction isolation level to repeateble read (using same connection - connection (*1)) , and i dont use any transaction yet, executoin of following statements

set transaction isolation level repeatable read;

select Q.path from openquery(filesystem,'select path from "localhost"."IndexCatalog"..SCOPE() ') AS Q

resutls in error above.

also when i then (after set transaction isolation level repeatable read set transaction isolation level to any other (just setting islolation level, not even using a trnasaction), it results in same error

set transaction isolation level READ UNCOMMITTED 
select Q.path from openquery(filesystem,'select path from "localhost"."IndexCatalog"..SCOPE() ') AS Q

set transaction isolation level READ COMMITTED 
select Q.path from openquery(filesystem,'select path from "localhost"."IndexCatalog"..SCOPE() ') AS Q

set transaction isolation level SERIALIZABLE 
select Q.path from openquery(filesystem,'select path from "localhost"."NutisCatalog"..SCOPE() ') AS Q

set transaction isolation level REPEATABLE READ 
select Q.path from openquery(filesystem,'select path from "localhost"."NutisCatalog"..SCOPE() ') AS Q

TypedListDao.GetMulti method uses always new SqlConnection, but it sometimes result in that error.

And i don't know where the problem is

i hope someone can help

albsoft AT gmail.com

using

 asp.NET11, IIS 5.1,
 SQL Server 2000 SP3a (Sql Server Standard Editon SP3a 8.00.760)
 Microsoft Windows XP, Proffesional, Version 2002, Service Pack 2

It does appear to be a locking issue, specifically with the Indexing Service. The error seems to be saying that the TRANSACTION ISOLATION LEVEL you specified doesn't work with the Indexing Service. Have you tried running the code without any locking schemes specified?

Also, could you let us know about your database (looks like a version of SQL Server), llblgen pro version, etc. according to the posting guidelines:

http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7717

Thanks.