USE MyLocalServer GO

IF OBJECT_ID('ListIDXFiles') IS NOT NULL BEGIN DROP PROCEDURE ListIDXFiles IF OBJECT_ID('ListIDXFiles') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE ListIDXFiles >>>' ELSE PRINT '<<< DROPPED PROCEDURE ListIDXFiles >>>' END GO

CREATE PROCEDURE ListIDXFiles ( @cmdID int = 0 , @columns nvarchar(500) = NULL , @scope nvarchar(255) = NULL , @contains nvarchar(500) = NULL , @order nvarchar(255) = NULL , @fileNameLike nvarchar(255) = NULL )

AS

-- Description: List Files in Index Server Scope. -- Developer: Bryan Wilhite

SET NOCOUNT ON

IF NOT EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname = 'IDXSERVER') BEGIN EXECUTE sp_addlinkedserver @server = IDXSERVER , @srvproduct = 'Index Server' , @provider = 'MSIDXS' , @datasrc = 'Web' --'Web' is the name of Index Server's default text search catalog. END

DECLARE @tSQL nvarchar(4000) , @test nvarchar(500) , @sq char(1)

IF @cmdID = 0 BEGIN IF @columns IS NULL SET @columns = 'DocTitle,Directory,Vpath,Path,ShortFileName,Filename,Size,Access,Create,Write,Characterization,Rank'

IF @scope IS NULL
    SET @scope = '"/"'

IF @order IS NULL
    SET @order = 'Rank DESC'

IF @fileNameLike IS NULL
    SET @fileNameLike = '%.pdf'

SET @sq = CHAR(39)

IF @contains IS NOT NULL
BEGIN
    -- Test only to prevent syntax errors.
    SET @test = @contains
    SET @test = REPLACE(@test,CHAR(34),'')
    SET @test = REPLACE(@test,CHAR(39),'')
    SET @test = REPLACE(@test,SPACE(1),'')
    IF LEN(@test) = 0
        RETURN

    -- The use of NEAR() on the client causes "Expecting Phrase" error in .HTW files.
    IF CHARINDEX(CHAR(34),@contains) = 0 AND CHARINDEX('NEAR',@contains) > 0 AND CHARINDEX('NEAR()',@contains) = 0
        SET @contains = REPLACE(@contains,'NEAR','NEAR()')

    SET @contains = REPLICATE(@sq,2) + @contains + REPLICATE(@sq,2)

    SET @tSQL = 'SELECT * FROM OPENQUERY(IDXSERVER,'
        + @sq + 'SELECT' + SPACE(1) + @columns
        + SPACE(1) + 'FROM SCOPE('+ REPLICATE(@sq,2) + @scope + REPLICATE(@sq,2) + ')'
        + SPACE(1) + 'WHERE CONTAINS(' + @contains + ') > 0'
        + SPACE(1) + 'AND Filename LIKE' + SPACE(1) + REPLICATE(@sq,2) + @fileNameLike + REPLICATE(@sq,2)
        + SPACE(1) + 'ORDER BY' + SPACE(1) + @order + @sq + ')'
END
ELSE
BEGIN
    SET @tSQL = 'SELECT * FROM OPENQUERY(IDXSERVER,'
        + @sq + 'SELECT' + SPACE(1) + @columns
        + SPACE(1) + 'FROM SCOPE('+ REPLICATE(@sq,2) + @scope + REPLICATE(@sq,2) + ')'
        + SPACE(1) + 'WHERE Filename LIKE' + SPACE(1) + REPLICATE(@sq,2) + @fileNameLike + REPLICATE(@sq,2)
        + SPACE(1) + 'ORDER BY' + SPACE(1) + @order + @sq + ')'
END

--For debugging purposes:
--PRINT @tSQL
--RETURN

EXECUTE sp_executesql @stmt = @tSQL

END RETURN GO

GRANT EXECUTE ON ListIDXFiles TO [Web Solutions Users] GO

IF OBJECT_ID('ListIDXFiles') IS NOT NULL PRINT '<<< CREATED PROCEDURE ListIDXFiles >>>' ELSE PRINT '<<< FAILED CREATING PROCEDURE ListIDXFiles >>>' GO