Click here to Skip to main content
15,170,971 members
Please Sign up or sign in to vote.
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
Comments
   
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

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900