Click here to Skip to main content
15,125,968 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have the view in sql, gives "MAXIMUM LENGTH 8000"

Please resolve. Thanks in advance.

SELECT     Type, Invoice, Receipt, InvoiceDt, Total, PaidToDate, ReceiptDt, NoDocSum, CardCode, CardName, SaleExecutiveName, GroupName, slpName, DelTrd, SALETYPE,
                      DATEDIFF(day, InvoiceDt, GETDATE()) AS Daycount, CredLimit
FROM         OPENQUERY(sapsvr, 
                      '
SELECT  distinct ''Bill'' as Type, T0.DocNum AS''Invoice'', T1.DocNum AS''Receipt'', T0.DocDate AS''InvoiceDt'',case  when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end as Total, 
 T0.PaidToDate , 
                      G.DocDate  AS''ReceiptDt'', G.NoDocSum, T0.CardCode, T0.CardName,
                      dbo.OHEM.firstName + '' '' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCQG.GroupName,OSLP.slpName,CAST (T0.U_Sales_Type AS VARCHAR(100)) AS SALETYPE ,cast(dbo.ocrd.u_customertype as varchar(100)) as  DelTrd ,dbo.ocrd.U_CreditLimit  as CredLimit
FROM     (select  NoDocSum,DocDate,DocNum,docentry from  dbo.ORCT where CANCELED =''N'' ) G INNER JOIN
                      dbo.RCT1 AS T1 ON G.DocNum = T1.DocNum right outer join 
                      dbo.OINV AS T0 ON T0.ReceiptNum = G.docentry inner join dbo.ocrd  on T0.CardCode = dbo.ocrd.CardCode
					 LEFT OUTER JOIN
                      dbo.OCQG ON dbo.OCRD.GroupNum = dbo.OCQG.GroupCode LEFT OUTER JOIN
                      dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN
                       OSLP on dbo.OCRD.slpcode=OSLP.slpcode
 inner join ojdt T3 on T0.TransId=T3.TransId 
inner join jdt1 T4 on (T4.TransId=T3.TransId 
and T4.shortname= T0.cardcode )
WHERE     (T0.CANCELED =''N'')  AND (T0.DocStatus =''o'') AND (T0.DocTotal - T0.PaidToDate > 0) 

 and dbo.ocrd.cardType=''c''




union all


SELECT distinct  ''Bill'' as Type, T0.DocNum AS ''Invoice'', T1.DocNum AS ''Receipt'' , T0.DocDate AS''InvoiceDt'',case  when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end as Total , T1.DocTotal as PaidToDate,
                      T1.DocDate  AS ''ReceiptDt'', null as''NoDocSum'', T0.CardCode, T0.CardName,
                      dbo.OHEM.firstName + '' '' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCQG.GroupName,OSLP.slpName,CAST (T0.U_Sales_Type AS VARCHAR(50)) AS SALETYPE , cast(dbo.ocrd.u_customertype as varchar(100)) as  DelTrd ,dbo.ocrd.U_CreditLimit  as CredLimit 
FROM      dbo.ORIN T1 inner join     dbo.rin1 T3  on T3.docentry=T1.docentry INNER JOIN
                       dbo.OINV AS T0 ON T3.BaseRef = T0.DocNum and T0.CardCode = T1.CardCode inner join dbo.ocrd  on T0.CardCode = dbo.ocrd.CardCode
					 LEFT OUTER JOIN
                      dbo.OCQG ON dbo.OCRD.GroupNum = dbo.OCQG.GroupCode LEFT OUTER JOIN
                      dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN
                     OSLP on dbo.OCRD.slpcode=OSLP.slpcode
WHERE     (T0.CANCELED =''N'')  AND (T0.DocStatus =''o'') AND (T0.DocTotal - T1.DocTotal > 0) and dbo.ocrd.cardType=''c'' and T3.BaseType=15

 
union all



SELECT  distinct  ''Receipt'' as Type, dbo.ORCT.DocNum AS ''Invoice'',
 T0.Docnum 
,dbo.ORCT.DocDate AS ''Invoice Dt'' ,case  when dbo.ORCT.cashsum >0 then dbo.ORCT.cashsum 
else dbo.ORCT.checksum end as Total, ( case  when dbo.ORCT.cashsum >0 then dbo.ORCT.cashsum 
else dbo.ORCT.checksum end -dbo.ORCT.OpenBal) as PaidToDate, T0.DocDate AS ''Receipt Dt'' , dbo.ORCT.NoDocSum,  dbo.ORCT.CardCode,  dbo.ORCT.CardName,
                      dbo.OHEM.firstName + '' '' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCQG.GroupName,OSLP.slpName,CAST (T0.U_Sales_Type AS VARCHAR(50)) AS SALETYPE ,cast(dbo.ocrd.u_customertype as varchar(100)) as  DelTrd ,dbo.ocrd.U_CreditLimit  as CredLimit 
FROM         dbo.ORCT 
      left outer join 
             dbo.RCT1 AS T1 ON dbo.ORCT.DocNum = T1.DocNum 
       left outer join 
             dbo.OINV AS T0 ON  T0.ReceiptNum = dbo.ORCT.DocEntry 
         inner  join 
            dbo.ocrd  on dbo.ORCT.CardCode = dbo.ocrd.CardCode
       LEFT OUTER JOIN
                      dbo.OCQG ON dbo.OCRD.GroupNum = dbo.OCQG.GroupCode
       LEFT OUTER JOIN
            dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID
       LEFT OUTER JOIN
           OSLP on dbo.OCRD.slpcode=OSLP.slpcode 
         inner  join 
          ojdt T3 on dbo.ORCT.TransId=T3.TransId 
         inner  join 
        jdt1 T4 on (T4.TransId=T3.TransId and T4.shortname= dbo.ORCT .cardcode )
WHERE 
(dbo.ORCT.CANCELED = ''N'') and 
 dbo.ORCT.OpenBal>0 
and  dbo.ocrd.cardType=''c''

union all


SELECT  distinct  ''CR'' AS Type ,T0.DocNum,null as Receipt,T0.DocDate,case  when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end as Total , T0.PaidToDate,null AS ReceiptD, null as NoDocSum,  T0.CardCode, T0.CardName, 
                      dbo.OHEM.firstName + '' '' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCQG.GroupName,OSLP.slpName,CAST (T0.U_Sales_Type AS VARCHAR(50)) AS SALETYPE ,cast(dbo.ocrd.u_customertype as varchar(100)) as  DelTrd , dbo.ocrd.U_CreditLimit  as CredLimit 
                     
FROM         dbo.ORIN T0 inner join dbo.ocrd  on T0.CardCode = dbo.ocrd.CardCode
						 LEFT OUTER JOIN
                      dbo.OCQG ON dbo.OCRD.GroupNum = dbo.OCQG.GroupCode LEFT OUTER JOIN
                      dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN
                      OSLP on dbo.OCRD.slpcode=OSLP.slpcode
WHERE     (T0.PaidToDate = 0)and (T0.DocTotal - T0.PaidToDate)>0 and T0.DocStatus<>''c''  and dbo.ocrd.cardType=''c''  

union all
SELECT   distinct ''JDN'' AS Type ,T3.TransId,null as Receipt,T3.RefDate,case  when T0.Debit >0 then T0.Debit else T0.Debit end as Total , null as PaidToDate,null AS ReceiptD, null as NoDocSum,  dbo.ocrd.CardCode, dbo.ocrd.CardName,
                      dbo.OHEM.firstName + '' '' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCQG.GroupName,OSLP.slpName,CAST (T0.U_Sales_Type AS VARCHAR(50)) AS SALETYPE ,cast(dbo.ocrd.u_customertype as varchar(100)) as  DelTrd , dbo.ocrd.U_CreditLimit  as CredLimit                      
FROM       ojdt T3 inner join   dbo.JDT1 T0 on  T3.TransId=T0.TransId  inner join dbo.ocrd  on T0.ShortName = dbo.ocrd.CardCode
						 LEFT OUTER JOIN
                      dbo.OCQG ON dbo.OCRD.GroupNum = dbo.OCQG.GroupCode LEFT OUTER JOIN
                      dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN
                     OSLP on dbo.OCRD.slpcode=OSLP.slpcode

where T3.TransType=30  and T0.MthDate is null and dbo.ocrd.cardType=''c'' 

  union all

SELECT distinct  ''JCN'' AS Type ,T3.TransId,null as Receipt,T3.RefDate,case  when T0.credit>0 then T0.credit else T0.credit end as Total , null as PaidToDate,null AS ReceiptD, null as NoDocSum,  dbo.ocrd.CardCode, dbo.ocrd.CardName,
                      dbo.OHEM.firstName + '' '' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCQG.GroupName,OSLP.slpName,CAST (T0.U_Sales_Type AS VARCHAR(50)) AS SALETYPE ,cast(dbo.ocrd.u_customertype as varchar(100)) as  DelTrd , dbo.ocrd.U_CreditLimit  as CredLimit 
                     
FROM       ojdt T3 inner join   dbo.JDT1 T0 on  T3.TransId=T0.TransId  inner join dbo.ocrd  on T0.ShortName = dbo.ocrd.CardCode
						 LEFT OUTER JOIN
                      dbo.OCQG ON dbo.OCRD.GroupNum = dbo.OCQG.GroupCode LEFT OUTER JOIN
                      dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN
                    OSLP on dbo.OCRD.slpcode=OSLP.slpcode

where T3.TransType=30   and T0.MthDate is null  and dbo.ocrd.cardType=''c''  
union all
SELECT   distinct ''Out_Pay'' AS Type ,T0.DocNum,null as Receipt,T0.DocDate,case  when T0.DocTotal >0 then T0.DocTotal else T0.DocTotal end as Total , null PaidToDate,null AS ReceiptD, T0. NoDocSum,  T0.CardCode, T0.CardName,
                      dbo.OHEM.firstName + '' '' + dbo.OHEM.lastName AS SaleExecutiveName, dbo.OCQG.GroupName,OSLP.slpName,CAST (T0.U_Sales_Type AS VARCHAR(50)) AS SALETYPE ,cast(dbo.ocrd.u_customertype as varchar(100)) as  DelTrd , dbo.ocrd.U_CreditLimit  as CredLimit 
                     
FROM         dbo.OVPM T0 inner join dbo.ocrd  on T0.CardCode = dbo.ocrd.CardCode
						 LEFT OUTER JOIN
                      dbo.OCQG ON dbo.OCRD.GroupNum = dbo.OCQG.GroupCode LEFT OUTER JOIN
                      dbo.OHEM ON dbo.OCRD.DfTcnician = dbo.OHEM.empID LEFT OUTER JOIN
                       OSLP on dbo.OCRD.slpcode=OSLP.slpcode
					 inner join ojdt T3 on T0.TransId=T3.TransId 
inner join jdt1 T4 on (T4.TransId=T3.TransId 
and T4.shortname= T0.cardcode )
WHERE     (T0.NoDocSum <> 0)and  T0.Doctype=''c''  and dbo.ocrd.cardType=''c''
and t4.MthDate is null  





')
                       AS derivedtbl_1


Edit: Removed Shouting. All upper case is consider shouting and rude in the internet.
Posted
Updated 2-Dec-13 1:33am
v4

1 solution

I just copy & pasted your query into Sql Server Management Studio and let it parse the query.

This is the error:
Msg 103, Level 15, State 1, Line 4
The character string that starts with '
SELECT distinct 'Bill' as Type, T0.DocNum AS'Invoice', T1.DocNum AS'Receipt', T0.DocDate AS'InvoiceDt',case when T0.DocTotal ' is too long. Maximum length is 8000.


The problem is: A string (or varchar) in a query is limited to 8000 characters (or 4000 for nvarchar)
The subquery you're trying to run with "OPENQUERY" contains more than those 8000 characters. So you need to edit that query to have fewer than those 8000 characters.

Possible ways to solve this issue:
- split that query into multiple smaller queries
- create a stored procedure on the server where you want to get that data from an replace that monstrosity of a query with a call to the stored procedure
   
Comments
Master Vinu 2-Dec-13 6:45am
   
which way i can split the query??please guide the same.
Nicholas Marty 2-Dec-13 6:48am
   
You're doing lots of UNION ALL. How about just call each select one by one and do the union in the main query?
Master Vinu 2-Dec-13 6:49am
   
could y please make one change , so rest i will manage same
Master Vinu 2-Dec-13 7:02am
   
error occoured when m trying to add "CAST (T0.U_Sales_Type AS VARCHAR(50)) AS SALETYPE "

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




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