Hi guys,I solved this issue by split the input parameters into several parts (each time 100 input parameters will be pass to the main query) and loop the main query to get all records and store in a temp table. Below is the solution:
DECLARE @LineIds AS NVARCHAR(MAX)
DECLARE @lindIdCounter as int
SELECT @LineIds = [Thousands of records]
DECLARE @lineIdtemptable TABLE (IdCounter int identity,lineId BIGINT)
INSERT INTO @lineIdtemptable(lineId)
SELECT * FROM dbo.Split(@LineIds,',')
SELECT @lindIdCounter = count(*) FROM @lineIdtemptable
DECLARE @BatchCount INT
DECLARE @remainingrecords INT
DECLARE @RecCount INT
SELECT @RecCount = 0
SELECT @BatchCount = 100
SELECT @remainingrecords = @lindIdCounter
DECLARE @Sql VARCHAR(MAX)
DECLARE @lineIdPara AS VARCHAR(MAX)
SELECT @LINEIDPARA = ''
WHILE (@remainingrecords>0)
BEGIN
SELECT @RECCOUNT = @RECCOUNT + @BatchCount
SELECT @LINEIDPARA = @LINEIDPARA + CONVERT(VARCHAR,COALESCE(LINEID,'')) + ','
FROM @LINEIDTEMPTABLE
WHERE IDCOUNTER BETWEEN @RECCOUNT-@BATCHCOUNT+1 AND @RECCOUNT
SELECT @LINEIDPARA = SUBSTRING(@LINEIDPARA,0,LEN(@LINEIDPARA))
select @remainingrecords = @remainingrecords - @RECCOUNT
select @Sql = [Your query block]
SET @Sql = 'INSERT INTO #myMainTable SELECT * FROM OPENQUERY(BILLYSTAGE, ''' + REPLACE(@Sql, '''', '''''') + ''') SDR'
EXEC(@Sql)
SELECT @LINEIDPARA = ''
END
Thanks everyone!!!