asked on

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

Qlemo

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 ;-).

Aaron Tomosky

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.

Qlemo

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.

Zberteoc

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.

Zberteoc

Actually it would be safer to use:

DECLARE @sql VARCHAR(8000)

instead of MAX.

Zberteoc

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

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thayduck

ASKER

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