August 3, 2009 at 7:27 am
Hello folks,
this statement I wrote a while ago does not work with a wildcard, as I recognized.
DECLARE @strSQL NVARCHAR(MAX)
SELECT @strSQL= 'SELECT FileName, path, size, vpath from "GRIP-SERVER"."Web2"..SCOPE() where contains
('SELECT @strSQL=@strSQL + CHAR(39) + CHAR(39)+ 'reiseportal*' + CHAR(39) + CHAR(39)+')'
SELECT @strSQL='SELECT DISTINCT DOC.ID_Kandidat, IDXS.* FROM
OPENQUERY([GRIP-SERVER],'+ CHAR(39) + @strSQL + CHAR(39) +') AS IDXS INNER JOIN
tblK_Dokumente AS DOC
ON DOC.Link = IDXS.[FileName]
ORDER BY ID_Kandidat'
EXEC sp_executesql @statement = @strSQL
I use this statement within an SP (with a variable for the search phrase).
GRIP-SERVER is the linked Index Server.
I don't get it ... it returns results with "reiseportals" but not "reiseportal*". Do you have any hints for me?
Your help is greatly appreciated, thanks a lot!
August 3, 2009 at 7:52 am
My first thought was to have you write a sproc on the linked server that you simply called instead of building a dynamic sql statement to pass to it.
But as I got to looking, are you sure you are using the CONTAINS statement correctly, it doesn't seem to fit the structure as defined by SQL 2005 BOL Nov 2008 at:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/996c72fc-b1ab-4c96-bd12-946be9c18f84.htm
I would see if my statement is correct..
CEWII
August 4, 2009 at 3:09 am
the linked server isnt a SQL server, but a windows server. I just access the index server of it. I'm pretty sure the statement is correct, cause it returns results without wildcards.
I'm already using the statement inside a sproc, these are the contents of the @strSQL variable:
after 1st select:
select FileName, path, size, vpath from "GRIP-SERVER"."Web2"..SCOPE() where
contains(''reiseportal*'')
2nd select:
SELECT DISTINCT DOC.ID_Kandidat, IDXS.* FROM
OPENQUERY([GRIP-SERVER],'select FileName, path, size, vpath from "GRIP-SERVER"."Web2"..SCOPE() where
contains(''reiseportal*'')') AS IDXS INNER JOIN
tblK_Dokumente AS DOC
ON DOC.Link = IDXS.[FileName]
ORDER BY ID_Kandidat'
August 4, 2009 at 6:16 am
I found the answer myself.
The problem is not the wildcard, but the string-markers. The index server does not use apostrophes but quotation marks. So the correct SQL should be like:
SELECT DISTINCT DOC.ID_Kandidat, IDXS.* FROM
OPENQUERY([GRIP-SERVER],'select FileName, path, size, vpath from "GRIP-SERVER"."Web2"..SCOPE() where
contains(''"reiseportal*"'')') AS IDXS INNER JOIN
tblK_Dokumente AS DOC
ON DOC.Link = IDXS.[FileName]
ORDER BY ID_Kandidat
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply