字符串“"太长.最大长度为8000-Openquery [英] the character string '' is too long. Maximum length is 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

推荐答案

大家好，我通过拆分输入参数解决了这个问题分成几部分(每次将100个输入参数传递给主查询)，然后循环主查询以获取所有记录并存储在临时表中.解决方法如下:

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)      -- 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!!!

使用Openquery时有另一种语法，该语法应允许需要的字符数.一如既往的YMMV.我的40,000个char脚本工作正常.
另一个优点是，使用这种语法可以使单引号的转义变得简单一些.它们仍然需要转义，但引号要少一些.
示例:

There is an alternate syntax when using Openquery that should allow as many characters as needed. As always YMMV. I have had a 40,000 char script work fine.
One other advantage is that the escaping of single quotes is a little less complicated with this syntax. They still need to be escaped, but with less sets of quotes.
Example:

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！