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