Trouble creating a query

[Jeff Circeo]

I have an asp application that allows users to upload files and search there
content. I am using IIS security but not on the file system

This is my query.
SELECT Filename, Size, Vpath, Path, Write, Characterization, hitcount FROM
Folders..SCOPE('DEEP TRAVERSAL OF
"C:\Folders\Documents\ds100\UsersArchive\admin",
"C:\Folders\Documents\ds100\Archive\Public",
"C:\Folders\Documents\ds100\Archive\IT Information",
"C:\Folders\Documents\ds100\Archive\30-1193",
"C:\Folders\Documents\ds100\Archive\20-898"') WHERE
CONTAINS('("*test*" )')>0 order by hitcount desc

This query is created by doing a lookup in a database to return allowed
folders and builds the query based on what the user is allowed access to,
the problem is that I have over a 500 folders so this query gets very long
fast depending on the user, as it is now if a user has full access this
query is about 27,464 characters long.

I am getting this error
Error number, -2147024809, The parameter is incorrect.



This error does not seem right to me is there a limit on how long the query
can be say under 15,000 characters long (this query length works)

If I restrict access to reduce the query string length to about half, it
works.

I am unsure on how to resolve this, I have been searching for 5 days now
with no luck.



Any help would be greatly appreciated

Thanks,

Jeff

[Hilary Cotter]

Here is something that works for me.

strSelect='SELECT Filename, Size, Vpath, Path, Write, Characterization,
hitcount FROM
web..SCOPE(''DEEP TRAVERSAL OF
"C:\Folders\Documents\ds100\UsersArchive\admin",
"C:\Folders\Documents\ds100\Archive\Public",
"C:\Folders\Documents\ds100\Archive\IT Information",
"C:\Folders\Documents\ds100\Archive\30-1193",
"C:\Folders\Documents\ds100\Archive\20-898"'') WHERE
contents like ''%test%'' order by hitcount desc'
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Jeff Circeo
I have an asp application that allows users to upload files and search
there content. I am using IIS security but not on the file system
This is my query.
SELECT Filename, Size, Vpath, Path, Write, Characterization, hitcount FROM
Folders..SCOPE('DEEP TRAVERSAL OF
"C:\Folders\Documents\ds100\UsersArchive\admin",
"C:\Folders\Documents\ds100\Archive\Public",
"C:\Folders\Documents\ds100\Archive\IT Information",
"C:\Folders\Documents\ds100\Archive\30-1193",
"C:\Folders\Documents\ds100\Archive\20-898"') WHERE
CONTAINS('("*test*" )')>0 order by hitcount desc
This query is created by doing a lookup in a database to return allowed
folders and builds the query based on what the user is allowed access to,
the problem is that I have over a 500 folders so this query gets very long
fast depending on the user, as it is now if a user has full access this
query is about 27,464 characters long.
I am getting this error
Error number, -2147024809, The parameter is incorrect.
This error does not seem right to me is there a limit on how long the
query can be say under 15,000 characters long (this query length works)
If I restrict access to reduce the query string length to about half, it
works.
I am unsure on how to resolve this, I have been searching for 5 days now
with no luck.
Any help would be greatly appreciated
Thanks,
Jeff