SQL and ms Indexing Service combined query causing AV Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL and ms Indexing Service combined query causing AV Error

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-05 : 10:44:19

Omer writes "Hello,

my goal is to add an extended search to a website, which will include
searching the requested term both in by database and in
files(pdf,doc,ppt....) uploaded by the admins to a directory on the site.

not all files should be accessible to all users, some are related to
articles which is now marked as hidden and some are related to
articles which marked as special permissions required.

all permissions data are in my db on the articles that relate the
files

im using MS SQL2K SP4 on WIN2K3 SP1, and all tests are done in the Query
Analyzer
so what ive done so far is this:
crating a linked server to the Indexing Service(MSIDXS) in the sql
server as MyLinkedServer

this query works but takes along time to complete(over a minute):

select * from myArticlesCombiningView where
convert(varchar(8000),content) in
(
select vpath from openquery(MyLinkedServer,'select vpath from scope()
where freetext(''SearchString'') order by rank desc')
)

myArticlesCombiningView is a view combining all my sections currently
returning over 27K records
on the test i used a very common search string wich the users my
submit and it returned about 450 files/records from the Indexing
service

so ive tried to make it lighter and made the following query after
consulting 2 colleagues"

select * from myArticlesCombiningView where
(
select vpath from openquery(MyLinkedServer,'select vpath from scope()
where freetext(''SearchString'')') where
convert(varchar(8000),content)=vpath
) is not null

this gives me th following error
"
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 63 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Connection Broken
"
ive found some articles/posts about Access Violation in SQL all
telling that it is caused or by a db corruption or by a bug in SQL
Server

so ive checked the db and its fine,

i will appreciate some advice on this error or a different method to
accomplish my task

thanks
Omer"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources