April 30, 2003 at 4:09 pm
I have a problem with "@scop" !!
when i put a value in "@scop" i get nothing !!
and when i put the value direct here
like this it work OK !!
FROM SCOPE (''"c:\"'')
--------------------------------
DECLARE @search VARchar(3000)
DECLARE @scop VARchar(20)
set @scop='C:\'
set @search='ilan '
SELECT TOP 10 *
FROM OPENQUERY(NTindex,
'SELECT DocTitle,create,path,filename, rank,VPath,Contents,characterization
FROM SCOPE (''@scop'')
WHERE FREETEXT(Contents, ''"@search"'') '
)
--------------------------
thnks
ilan
April 30, 2003 at 5:21 pm
THIS IS MY ERROR :
--------------------
Server: Msg 7321, Level 16, State 2, Line 5
An error occurred while preparing a query for execution against OLE DB provider 'msidxs'.
[OLE/DB provider returned message: Incorrect syntax near 'ȁ'. SQLSTATE=42000 ]
May 2, 2003 at 9:06 am
The problem is you are sending the query with @scop and @search not the contents of the variables. You need to create the whole query (inc variable values) in a variable and exceute it.
DECLARE @search VARchar(3000)
DECLARE @scop VARchar(20)
DECLARE @sql nvarchar(1000)
set @scop='C:\'
set @search='ilan '
set @sql = 'SELECT TOP 10 *
FROM OPENQUERY(NTindex,
''SELECT DocTitle,create,path,filename, rank,VPath,Contents,characterization
FROM SCOPE ('''+@scop+''')
WHERE FREETEXT(Contents, ''"'+@search+'"'')'
exec sp_executesql @sql
Edited by - davidburrows on 05/02/2003 09:07:33 AM
Far away is close at hand in the images of elsewhere.
Anon.
May 2, 2003 at 10:52 am
ok thnks but i have an error
---------
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'C:'.
Server: Msg 105, Level 15, State 1, Line 5
Unclosed quotation mark before the character string ')'.
--------------
thnks ilan
May 2, 2003 at 11:03 am
Sorry, cockup on quotes (could'nt test)
DECLARE @search VARchar(3000)
DECLARE @scop VARchar(20)
DECLARE @sql nvarchar(1000)
set @scop='C:\'
set @search='ilan '
set @sql = 'SELECT TOP 10 *
FROM OPENQUERY(NTindex,
''SELECT DocTitle,create,path,filename, rank,VPath,Contents,characterization
FROM SCOPE ('''''+@scop+''''')
WHERE FREETEXT(Contents, ''''"'+@search+'"'''')'')'
exec sp_executesql @sql
Far away is close at hand in the images of elsewhere.
Anon.
May 2, 2003 at 11:27 am
OHHHHHHHHH NO
-----------------------
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'msidxs'.
OLE DB error trace [OLE/DB Provider 'msidxs' ICommandPrepare::Prepare returned 0x80040e14].
--------------------------------------
May 2, 2003 at 5:07 pm
thanks i fix the problem
in The SCOPE instead of c:then "c:\"
you help my a lot
thnks again
ilan
May 3, 2003 at 11:35 am
HI I NEED YOUR HELP
what i wont to do is OREDR BY With "@ORDERBY"
------------------------------------
DECLARE @search VARchar(3000)
DECLARE @scop VARchar(20)
DECLARE @orderby VARchar(20)
DECLARE @sql nvarchar(1000)
set @scop='\'
set @orderby='Rank'
set @search='DOC '
set @sql = 'SELECT TOP 50 *
FROM OPENQUERY(NTindex,
''SELECT DocTitle,create,path,filename, rank,VPath,Contents,characterization
FROM SCOPE (''''"'+@scop+'"'''')
WHERE FREETEXT(Contents, '''''+@search+''''')'')
WHERE (FILENAME LIKE N''%.pdf'') OR
(FILENAME LIKE N''%.doc'') OR
(FILENAME LIKE N''%.xls'') OR
(FILENAME LIKE N''%.ppt'') OR
(FILENAME LIKE N''%.txt'')
AND (NOT (VPATH LIKE N''%\_vti%'')) AND (NOT (VPATH LIKE N''%/_private%'')) AND (NOT (PATH LIKE N''%\winnt%''))
ORDER BY (''+@orderby+'') DESC '
exec sp_executesql @sql
GO
---------------------
THNKS ILAN
May 6, 2003 at 1:22 am
Set the quotes as:
'SELECT * FROM tbl_Table
ORDER BY ('+ @orderby +') DESC'
This gives:
ORDER BY (Rank) DESC
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy