SQL query to linked index server does not work w wildcards

Question

Hello folks,

this statement I use in a sproc (where the search phrase 'reiseportal*' is a parameter) 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

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'

GRIP-SERVER is the linked Index Server (=Microsoft Server 2003 - our file server).

I don't get it ... it returns results with "reiseportals" but not "reiseportal*" or "reiseportal%". Do you have any hints for me?

Your help is greatly appreciated, thanks a lot!

Answer 1

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