The character string that starts too long. Maximum length is 8000

In my SQL Stored Procedure, I am reading records from Oracle tables then inserting them into a SQL temp table and then running query .

I have done this a lot with success. But now, on this particular query I am running into the below error:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'SC_procRptARIssues'. (rsErrorExecutingCommand)
The character string that starts with 'SELECT SUBSTR(GPCOMP1.GPRECL.CUSTNO,1,15) as CUSTNO, SUBSTR(GPCOMP1.GPRECL.CUSTNO,2,6) as CUSTNO6, GPCOMP1.GPRECL.AMOUNT as AMOU' is too long. Maximum length is 8000.

We are not allowed to make any Linked Server changes and getting Linked Server changes done would take a Act Of Congress.

I have done some surfing and I see where people suggest splitting the SQL statement into multiple variables but I did not quite understand.  



It is failing on the below step:

SET @sql = N'INSERT INTO [#IssuesDetails]
            SELECT  * from openquery
            ([GETPAID],    '''
                 + Replace(@sql, '''', '''''') + ''')'

Open in new window

               

This is my declare.

DECLARE @sql VARCHAR(max).

Is there  a work around for this issue using multiple variables  or anything else ?

Below is the code to produce this error. Just thought I would show it.

DECLARE @sql VARCHAR(max)


SET @sql = 'SELECT  SUBSTR(GPCOMP1.GPRECL.CUSTNO,1,15) as CUSTNO, SUBSTR(GPCOMP1.GPRECL.CUSTNO,2,6) as CUSTNO6, GPCOMP1.GPRECL.AMOUNT as AMOUNT, GPCOMP1.GPRECL.TRANTYPE as TRANTYPE,       
            GPCOMP1.GPRECL.DUEDATE as DUEDATE, GPCOMP1.GPRECL.INVDATE as INVDATE,(CASE WHEN GPCOMP1.GPRECL.FLEXDATE1 IS NOT NULL AND GPCOMP1.GPRECL.FLEXDATE1 <> ''0001/01/01'' THEN GPCOMP1.GPRECL.FLEXDATE1             
            ELSE  SYSDATE END) as FLEXDATE1, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD2,1,5) as FLEXFIELD2, GPCOMP1.GPCUST.LIMIT as LIMIT,
            GPCOMP1.ARMAST.INVDTE AS ARINVDATE, GPCOMP1.ARMAST.PNET as TERMS, SUBSTR(GPCOMP1.GPCOLL.TEAM,1,10) as TEAM,
            GPCOMP1.GPCOLL.COLLNAME AS PORTFOLIONAME, GPCOMP1.GPCUST.COLLTYPE AS CUSTTYPE, GPCOMP1.GPCUST.COMPANY as COMPANY,             
            GPCOMP1.GPPROB.PROBLEM_ID AS ProblemID,GPCOMP1.GPPROB.STATUS as Status1, SUBSTR(GPCOMP1.GPPROB.UCONTENT01,1,255) AS DisputeDesc, 
                      GPCOMP1.GPREACAT.DESCRIPTION AS ReasonCategory, GPCOMP1.GPPCAT.PDESC AS Reason, 
                      GPCOMP1.GPOWNTYP.OWNDESC AS OwnerCategory, GPCOMP1.GPSLPN.SALEPERSON AS Owner1, GPCOMP1.GPPROB.SALESAREA AS SalesArea,                     
                      TO_CHAR(GPCOMP1.GPPROB.DE_DATE,''YYYY/MM/DD'') AS EscalationDate, 
                      GPCOMP1.GPRESLVR.RESNAME AS Resolver,
                      TRUNC(SYSDATE)-TRUNC(GPCOMP1.GPPROB.DE_DATE) as EscalationDaysDiff,                     
                      TRUNC(SYSDATE)-TRUNC(GPCOMP1.GPPROB_RECL.MODIFIED_ON) as StatusDays,                
                      GPCOMP1.GPSTATUS.DESCRIPTION AS StatusName, SUBSTR(GPCOMP1.GPPROB.UCONTENT02,1,30) AS ProblemExtended,
                      GPCOMP1.GPCOLL.COLLCODE as PortfolioCode, SUBSTR(GPCOMP1.GPPROB.UCONTENT03,1,255) AS NextStep, SUBSTR(GPCOMP1.GPPROB.UCONTENT04,1,255) AS Required1,
                      GPCOMP1.GPRECL.FLEXDATE3 AS FlexDate3            
                     
                      FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL, GPCOMP1.GPPROB_RECL, GPCOMP1.GPPROB, GPCOMP1.GPPCAT, GPCOMP1.GPREACAT, 
                              GPCOMP1.GPSLPN, GPCOMP1.GPOWNTYP, GPCOMP1.GPCOLL, GPCOMP1.GPRESLVR, GPCOMP1.GPUSER GPUSER_1, GPCOMP1.GPUSER,
                              GPCOMP1.GPSTATUS, GPCOMP1.ARMAST 
                                                    
                      WHERE   GPCOMP1.GPRECL.CUSTNO = GPCOMP1.GPCUST.CUSTNO AND GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID AND 
                      GPCOMP1.GPPROB_RECL.PROBLEM_ID = GPCOMP1.GPPROB.PROBLEM_ID AND GPCOMP1.GPPROB.PCODE = GPCOMP1.GPPCAT.PCODE AND 
                      GPCOMP1.GPPCAT.REASCAT = GPCOMP1.GPREACAT.CATEGORY AND GPCOMP1.GPPROB.SALESID = GPCOMP1.GPSLPN.SALESID AND 
                      GPCOMP1.GPSLPN.OWNTYPE = GPCOMP1.GPOWNTYP.OWNTYPE AND GPCOMP1.GPCUST.COLLECTOR = GPCOMP1.GPCOLL.COLLCODE AND 
                      GPCOMP1.GPPROB.RESOLVER = GPCOMP1.GPRESLVR.RESCODE AND GPCOMP1.GPPROB_RECL.MODIFIED_BY = GPUSER_1.USER_ID AND 
                      GPCOMP1.GPPROB.USER_ID = GPCOMP1.GPUSER.USER_ID AND (NOT (GPCOMP1.GPPROB.PROBLEM_ID IS NULL)) AND GPCOMP1.GPPROB.STATUS = GPCOMP1.GPSTATUS.CODE AND
                      GPCOMP1.GPRECL.CUSTNO = GPCOMP1.ARMAST.CUSTNO AND GPCOMP1.GPRECL.INVNO = GPCOMP1.ARMAST.INVNO AND GPCOMP1.GPCUST.INACTIVE = ''N'' AND 
                      GPCOMP1.GPPROB.CLOSEDATE is null AND  
                                         
                      GPCOMP1.GPCOLL.TEAM IN('''
                 + CONVERT(VARCHAR(MAX), @Teamo, 120)
                 + ''')  AND
                    
                      GPCOMP1.GPCUST.COLLTYPE IN('''
                 + CONVERT(VARCHAR(MAX), @CustomerTypeo, 120)
                 + ''') AND                         
                      GPCOMP1.GPPROB.STATUS IN('''
                 + CONVERT(VARCHAR(MAX), @DisputeStatuso, 120)
                 + ''') AND
                      GPCOMP1.GPOWNTYP.OWNTYPE IN('''
                 + CONVERT(VARCHAR(MAX), @OwnerCategoryo, 120)
                 + ''') AND  
                      GPCOMP1.GPREACAT.CATEGORY IN('''
                 + CONVERT(VARCHAR(MAX), @DisputeReasonCategoryo, 120)
                 + ''')  AND        
                      GPCOMP1.GPPROB.SALESID IN('''
                 + CONVERT(VARCHAR(MAX), @Ownero, 120)
                 + ''') AND                      
                      GPCOMP1.GPPCAT.PCODE IN('''
                 + CONVERT(VARCHAR(MAX), @DisputeReasonCodeo, 120)
                 + ''') AND
                      GPCOMP1.GPPROB.SALESAREA IN('''
                 + CONVERT(VARCHAR(MAX), @SalesAreao, 120)
                 + ''') AND
                      (  ( GPCOMP1.GPRESLVR.RESNAME IN('''
                 + CONVERT(VARCHAR(MAX), @ResolverNameo, 120)
                 + ''') ) or
                      ( '' Blank Name'' IN('''
                 + CONVERT(VARCHAR(MAX), @ResolverNameo, 120)
                 + ''') AND SUBSTR(GPCOMP1.GPRESLVR.RESNAME,1,1) not between ''A'' and ''Z'' )  )    '

   SET @sql = N'INSERT INTO [#IssuesDetails]
            SELECT  * from openquery
            ([GETPAID],    '''
                 + Replace(@sql, '''', '''''') + ''')'    
                 

      EXEC (@sql)

Open in new window

thayduckProgrammer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Stop using the fully qualified column names. Though it is good style to add the table name for each reference, doing a schema.table.columname all the time creates overly long SQL statements without adding anything.

The only schema used here is GPCOMP1, so that info is superfluous. If you can achieve to have the linked server using GPCOMP1 credentials only you do not need to provide that at all - but you said that is out of your influence scope.

My recommendation hence is to use table aliases, e.g. Cust for GPCOMP1.GPCUST and so on. Something like (showing parts of the SQL string only):
select SUBSTR(Recl.CUSTNO,1,15) as CUSTNO, SUBSTR(Recl.CUSTNO,2,6) as CUSTNO6, [...]
from GPCOMP1.GPCUST Cust, GPCOMP1.GPRECL Recl, [...]

Open in new window

I also recommend to use mixed case to improve readability. Having anything in uppercase hurts the eye, and usually you do not need to write anything in uppercase ;-).
0
Aaron TomoskySD-WAN SimplifiedCommented:
Are all those converts datetimes? If not you can just do
cast @name as varchar()

Also I believe you could drop of ton of those where's with an inner join.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Not related, and wouldn't change anything, but those converts generate single date strings, so using them in IN is strange - should be a simple = instead.

Aaron's recommendation does not help with the length issues, btw.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

ZberteocCommented:
From MSDN:

Syntax

OPENQUERY ( linked_server ,'query' )

Where

linked_server
Is an identifier representing the name of the linked server.
'query'
Is the query string executed in the linked server. The maximum length of the string is 8 KB.

Try this:
DECLARE @sql VARCHAR(max)
SET @sql = 'SELECT  SUBSTR(b.CUSTNO,1,15) as CUSTNO, SUBSTR(b.CUSTNO,2,6) as CUSTNO6, b.AMOUNT as AMOUNT, b.TRANTYPE as TRANTYPE,       
            b.DUEDATE as DUEDATE, b.INVDATE as INVDATE,(CASE WHEN b.FLEXDATE1 IS NOT NULL AND b.FLEXDATE1 <> ''0001/01/01'' THEN b.FLEXDATE1             
            ELSE  SYSDATE END) as FLEXDATE1, SUBSTR(b.FLEXFIELD2,1,5) as FLEXFIELD2, a.LIMIT as LIMIT,
            m.INVDTE AS ARINVDATE, m.PNET as TERMS, SUBSTR(i.TEAM,1,10) as TEAM,
            i.COLLNAME AS PORTFOLIONAME, a.COLLTYPE AS CUSTTYPE, a.COMPANY as COMPANY,             
            d.PROBLEM_ID AS ProblemID,d.STATUS as Status1, SUBSTR(d.UCONTENT01,1,255) AS DisputeDesc, 
                      f.DESCRIPTION AS ReasonCategory, e.PDESC AS Reason, 
                      h.OWNDESC AS OwnerCategory, g.SALEPERSON AS Owner1, d.SALESAREA AS SalesArea,                     
                      TO_CHAR(d.DE_DATE,''YYYY/MM/DD'') AS EscalationDate, 
                      j.RESNAME AS Resolver,
                      TRUNC(SYSDATE)-TRUNC(d.DE_DATE) as EscalationDaysDiff,                     
                      TRUNC(SYSDATE)-TRUNC(c.MODIFIED_ON) as StatusDays,                
                      l.DESCRIPTION AS StatusName, SUBSTR(d.UCONTENT02,1,30) AS ProblemExtended,
                      i.COLLCODE as PortfolioCode, SUBSTR(d.UCONTENT03,1,255) AS NextStep, SUBSTR(d.UCONTENT04,1,255) AS Required1,
                      b.FLEXDATE3 AS FlexDate3            
                     
                      FROM    GPCOMP1.GPCUST a, GPCOMP1.GPRECL b, GPCOMP1.GPPROB_RECL c, GPCOMP1.GPPROB d, GPCOMP1.GPPCAT e, GPCOMP1.GPREACAT f, 
                              GPCOMP1.GPSLPN g, GPCOMP1.GPOWNTYP h, GPCOMP1.GPCOLL i, GPCOMP1.GPRESLVR j, GPCOMP1.GPUSER GPUSER_1, GPCOMP1.GPUSER k,
                              GPCOMP1.GPSTATUS l, GPCOMP1.ARMAST m 
                                                    
                      WHERE   b.CUSTNO = a.CUSTNO AND b.TRAN_ID = c.TRAN_ID AND 
                      c.PROBLEM_ID = d.PROBLEM_ID AND d.PCODE = e.PCODE AND 
                      e.REASCAT = f.CATEGORY AND d.SALESID = g.SALESID AND 
                      g.OWNTYPE = h.OWNTYPE AND a.COLLECTOR = i.COLLCODE AND 
                      d.RESOLVER = j.RESCODE AND c.MODIFIED_BY = GPUSER_1.USER_ID AND 
                      d.USER_ID = k.USER_ID AND (NOT (d.PROBLEM_ID IS NULL)) AND d.STATUS = l.CODE AND
                      b.CUSTNO = m.CUSTNO AND b.INVNO = m.INVNO AND a.INACTIVE = ''N'' AND 
                      d.CLOSEDATE is null AND  
                                         
                      i.TEAM IN('''
                 + CONVERT(VARCHAR(MAX), @Teamo, 120)
                 + ''')  AND
                    
                      a.COLLTYPE IN('''
                 + CONVERT(VARCHAR(MAX), @CustomerTypeo, 120)
                 + ''') AND                         
                      d.STATUS IN('''
                 + CONVERT(VARCHAR(MAX), @DisputeStatuso, 120)
                 + ''') AND
                      h.OWNTYPE IN('''
                 + CONVERT(VARCHAR(MAX), @OwnerCategoryo, 120)
                 + ''') AND  
                      f.CATEGORY IN('''
                 + CONVERT(VARCHAR(MAX), @DisputeReasonCategoryo, 120)
                 + ''')  AND        
                      d.SALESID IN('''
                 + CONVERT(VARCHAR(MAX), @Ownero, 120)
                 + ''') AND                      
                      e.PCODE IN('''
                 + CONVERT(VARCHAR(MAX), @DisputeReasonCodeo, 120)
                 + ''') AND
                      d.SALESAREA IN('''
                 + CONVERT(VARCHAR(MAX), @SalesAreao, 120)
                 + ''') AND
                      (  ( j.RESNAME IN('''
                 + CONVERT(VARCHAR(MAX), @ResolverNameo, 120)
                 + ''') ) or
                      ( '' Blank Name'' IN('''
                 + CONVERT(VARCHAR(MAX), @ResolverNameo, 120)
                 + ''') AND SUBSTR(j.RESNAME,1,1) not between ''A'' and ''Z'' )  )    '

   SET @sql = N'INSERT INTO [#IssuesDetails]
            SELECT  * from openquery
            ([GETPAID],    '''
                 + Replace(@sql, '''', '''''') + ''')'    
                 

      select len(@sql) as '@sql_length' -- make sure this is <=8000 
      select @sql as '@sql'	/*
      select len(@sql)  --*/

Open in new window

Make sure that the @sql variable length is <=8000. First Just look what is inside the @sql variable by selecting from it instead of executing. Try to see if that script works if executed from a new window. You might need to change in SQL settings in result to grid the maximum size of the cell otherwise its content will be truncated.

If it works in the new window should work in the EXEC as well.
0
ZberteocCommented:
Actually it would be safer to use:

DECLARE @sql VARCHAR(8000)

instead of MAX.
0
ZberteocCommented:
Actually here is again with some corrections. If you want to toggle between

select @sql as '@sql'

and

EXEC(@sql)

just comment/uncomment the select @sql as '@sql' line.


DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT  SUBSTR(b.CUSTNO,1,15) as CUSTNO, SUBSTR(b.CUSTNO,2,6) as CUSTNO6, b.AMOUNT as AMOUNT, b.TRANTYPE as TRANTYPE,       
            b.DUEDATE as DUEDATE, b.INVDATE as INVDATE,(CASE WHEN b.FLEXDATE1 IS NOT NULL AND b.FLEXDATE1 <> ''0001/01/01'' THEN b.FLEXDATE1             
            ELSE  SYSDATE END) as FLEXDATE1, SUBSTR(b.FLEXFIELD2,1,5) as FLEXFIELD2, a.LIMIT as LIMIT,
            m.INVDTE AS ARINVDATE, m.PNET as TERMS, SUBSTR(i.TEAM,1,10) as TEAM,
            i.COLLNAME AS PORTFOLIONAME, a.COLLTYPE AS CUSTTYPE, a.COMPANY as COMPANY,             
            d.PROBLEM_ID AS ProblemID,d.STATUS as Status1, SUBSTR(d.UCONTENT01,1,255) AS DisputeDesc, 
                      f.DESCRIPTION AS ReasonCategory, e.PDESC AS Reason, 
                      h.OWNDESC AS OwnerCategory, g.SALEPERSON AS Owner1, d.SALESAREA AS SalesArea,                     
                      TO_CHAR(d.DE_DATE,''YYYY/MM/DD'') AS EscalationDate, 
                      j.RESNAME AS Resolver,
                      TRUNC(SYSDATE)-TRUNC(d.DE_DATE) as EscalationDaysDiff,                     
                      TRUNC(SYSDATE)-TRUNC(c.MODIFIED_ON) as StatusDays,                
                      l.DESCRIPTION AS StatusName, SUBSTR(d.UCONTENT02,1,30) AS ProblemExtended,
                      i.COLLCODE as PortfolioCode, SUBSTR(d.UCONTENT03,1,255) AS NextStep, SUBSTR(d.UCONTENT04,1,255) AS Required1,
                      b.FLEXDATE3 AS FlexDate3            
                     
                      FROM    GPCOMP1.GPCUST a, GPCOMP1.GPRECL b, GPCOMP1.GPPROB_RECL c, GPCOMP1.GPPROB d, GPCOMP1.GPPCAT e, GPCOMP1.GPREACAT f, 
                              GPCOMP1.GPSLPN g, GPCOMP1.GPOWNTYP h, GPCOMP1.GPCOLL i, GPCOMP1.GPRESLVR j, GPCOMP1.GPUSER GPUSER_1, GPCOMP1.GPUSER k,
                              GPCOMP1.GPSTATUS l, GPCOMP1.ARMAST m 
                                                    
                      WHERE   b.CUSTNO = a.CUSTNO AND b.TRAN_ID = c.TRAN_ID AND 
                      c.PROBLEM_ID = d.PROBLEM_ID AND d.PCODE = e.PCODE AND 
                      e.REASCAT = f.CATEGORY AND d.SALESID = g.SALESID AND 
                      g.OWNTYPE = h.OWNTYPE AND a.COLLECTOR = i.COLLCODE AND 
                      d.RESOLVER = j.RESCODE AND c.MODIFIED_BY = GPUSER_1.USER_ID AND 
                      d.USER_ID = k.USER_ID AND (NOT (d.PROBLEM_ID IS NULL)) AND d.STATUS = l.CODE AND
                      b.CUSTNO = m.CUSTNO AND b.INVNO = m.INVNO AND a.INACTIVE = ''N'' AND 
                      d.CLOSEDATE is null AND  
                                         
                      i.TEAM IN('''
                 + CONVERT(VARCHAR(MAX), @Teamo, 120)
                 + ''')  AND
                    
                      a.COLLTYPE IN('''
                 + CONVERT(VARCHAR(MAX), @CustomerTypeo, 120)
                 + ''') AND                         
                      d.STATUS IN('''
                 + CONVERT(VARCHAR(MAX), @DisputeStatuso, 120)
                 + ''') AND
                      h.OWNTYPE IN('''
                 + CONVERT(VARCHAR(MAX), @OwnerCategoryo, 120)
                 + ''') AND  
                      f.CATEGORY IN('''
                 + CONVERT(VARCHAR(MAX), @DisputeReasonCategoryo, 120)
                 + ''')  AND        
                      d.SALESID IN('''
                 + CONVERT(VARCHAR(MAX), @Ownero, 120)
                 + ''') AND                      
                      e.PCODE IN('''
                 + CONVERT(VARCHAR(MAX), @DisputeReasonCodeo, 120)
                 + ''') AND
                      d.SALESAREA IN('''
                 + CONVERT(VARCHAR(MAX), @SalesAreao, 120)
                 + ''') AND
                      (  ( j.RESNAME IN('''
                 + CONVERT(VARCHAR(MAX), @ResolverNameo, 120)
                 + ''') ) or
                      ( '' Blank Name'' IN('''
                 + CONVERT(VARCHAR(MAX), @ResolverNameo, 120)
                 + ''') AND SUBSTR(j.RESNAME,1,1) not between ''A'' and ''Z'' )  )    '

   SET @sql = N'INSERT INTO [#IssuesDetails]
            SELECT  * from openquery
            ([GETPAID],    '''
                 + Replace(@sql, '''', '''''') + ''')'    
                 

      select len(@sql) as '@sql_length' -- make sure this is <8000 
      select @sql as '@sql'	/*
      EXEC(@sql)  --*/

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That is almost exactly what I suggested. The main point is to make the overall query text shorter than 8000 characters. But if possible I would refrain from using "a, b, c" as aliases. The aliases should still tell something about the source.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
Well, you suggested and I delivered a solution. :)

Anyway, I agree in principle with Qlemo but in this case the goal is to make the code as short as possible and that is why I used single letters aliases. Beside that t was also faster for me to modify it. ;)

The point here is to keep that query under 8000 characters length so if you make it work you can then tweak the aliases if you want but in my opinion is not needed.
0
thayduckProgrammer AnalystAuthor Commented:
I like both similar suggestions and have made those changes and it did help.
But, some of these parameters will grow in size over time and I can see this problem popping up again.
Besides putting in aliases, I removed one of the parameters (largest one) from the Oracle statement and put it in the Select query portion of the stored procedure that does the final Select Query for the SSRS report..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.