the character string '' is too long. Maximum length is 8000 - Openquery

Question

0.00/5 (No votes)

See more:

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.

SQL

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

Posted 12-Dec-11 21:13pm

zzbsforever

Add a Solution

Comments

Sergey Alexandrovich Kryukov 13-Dec-11 3:19am

I'm just curious, why would you need such a long string? This is more of a blob...
--SA

[no name] 13-Dec-11 3:40am

agree!

zzbsforever 13-Dec-11 4:32am

I am working on a maintenance project which needs to pass some input parameters to openquery. Parameter Line_id(used in openquery) is got based on 'Project Code'(used in sql), this cause the problem which line_id is too many for one project_code.

Manish.Insan 13-Dec-11 4:11am

break the query in few parts using cte or temporary table.
may be this will help you.

zzbsforever 13-Dec-11 4:33am

I tried in this case, but temporary table is not support in openquery

Umar Adeeb 21-Jul-20 16:11pm

try this https://dba.stackexchange.com/questions/187522/8000-character-limit-on-openquery-against-a-linked-server

2 solutions

Solution 2

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>

Posted 20-Apr-12 5:26am

Member 7815631

Updated 5-Jun-12 10:58am

v2

Comments

zzbsforever 14-Oct-12 23:24pm

Thanks a lot. I have tried to verify this method. But I got this message:
Server '<linked Server Name>' is not configured for RPC.
I don't have permission to change the linked server options.But I think your method is better if it is working fine.

Umar Adeeb 21-Jul-20 16:11pm

https://dba.stackexchange.com/questions/187522/8000-character-limit-on-openquery-against-a-linked-server

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

zzbsforever · Accepted Answer · 2011-12-15T17:55:00

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:

SQL

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)

SQL

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

SQL

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

Thanks everyone!!!