字符串“"太长.最大长度为8000-Openquery

本文介绍了字符串“"太长.最大长度为8000-Openquery的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在SQL Server 2008中使用openquery时遇到问题.当我将输入参数传输到openquery时.下面的错误消息出来了，我可以看到输入参数太长，并且该错误是由于脚本的长度所致.有没有解决此问题的方法，请提供建议或解决方案.

I am facing a issue to use openquery in SQL server 2008. When I transfer input parameters to openquery. Below error message came out, I can see the input parameters are too long and the error is because of the length of the script. Is there a way to fix this problem, please provide a suggestion or a solution.

Msg 103, Level 15, State 1, Line 129
    The character string that starts with ' 
        SELECT BOMRank.PeggingLev 
              ,BOMRank.RowOrder 
              ,BOMRank.Pegging_Id 
              ,MFP.Organization_Id 
    ' is too long. Maximum length is 8000.
    Msg 102, Level 15, State 1, Line 242

推荐答案

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)      -- split the line ids to one table
SELECT * FROM dbo.Split(@LineIds,',')     -- This is a customized function to split strings
SELECT @lindIdCounter = count(*) FROM @lineIdtemptable   --Get the total number of line ids
DECLARE @BatchCount INT
DECLARE @remainingrecords INT
DECLARE @RecCount INT
SELECT @RecCount = 0
SELECT @BatchCount = 100
SELECT @remainingrecords = @lindIdCounter
DECLARE @Sql VARCHAR(MAX)

--Below query block will get 100 records step by step
DECLARE @lineIdPara AS VARCHAR(MAX)
SELECT @LINEIDPARA = ''
WHILE (@remainingrecords>0)
BEGIN
SELECT @RECCOUNT = @RECCOUNT + @BatchCount --record to fetch -- 100
SELECT @LINEIDPARA = @LINEIDPARA + CONVERT(VARCHAR,COALESCE(LINEID,'')) + ','  --Compose line ids to string
FROM @LINEIDTEMPTABLE
WHERE IDCOUNTER BETWEEN @RECCOUNT-@BATCHCOUNT+1 AND @RECCOUNT  -- Get 100 line ids
SELECT @LINEIDPARA = SUBSTRING(@LINEIDPARA,0,LEN(@LINEIDPARA)) -- removing the last comma from the lineid string
select @remainingrecords = @remainingrecords - @RECCOUNT  -- Get remaining line ids

select @Sql = [Your query block]
SET @Sql = 'INSERT INTO #myMainTable SELECT * FROM OPENQUERY(BILLYSTAGE, ''' + REPLACE(@Sql, '''', '''''') + ''') SDR'
EXEC(@Sql)
SELECT @LINEIDPARA = ''
END

谢谢大家！！！

Thanks everyone!!!

Declare @sql varchar(max) = '
Select datecol from table 
where datecol between ''1/1/2011'' --only 2 quotes per side instead of 4
                  and ''12/31/2011''  --only 2 quotes per side instead of 4
order by datecol
' 
exec(@sql) at <linked servername>

Declare @sql varchar(max) = '
--<your really long sql query here>
'
Exec(@sql) at <linked servername>

这篇关于字符串“"太长.最大长度为8000-Openquery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

OpenQuery