视图中最大长度为8000错误 [英] Maximum length 8000 error in view
本文介绍了视图中最大长度为8000错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在sql中有视图，给出MAXIMUM LENGTH 8000
请解决。提前致谢。
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
编辑：删除了喊叫。所有大写都是考虑在互联网上大喊大叫。
Removed Shouting. All upper case is consider shouting and rude in the internet.
推荐答案
我只是将你的查询复制并粘贴到Sql Server Management Studio中，让它解析查询。 />
这是错误：
消息103，级别15，状态1，行4
字符串以'
SELECT distinct'Bill'作为Type，T0.DocNum AS'Invoice'，T1.DocNum AS'Receipt'，T0.DocDate AS'InvoiceDt'，T0.DocTotal'为太长。最大长度为8000.
问题是：查询中的字符串（或varchar）限制为8000个字符（或4000个字符） for nvarchar）
您尝试使用OPENQUERY运行的子查询包含超过8000个字符。因此，您需要编辑该查询，使其少于8000个字符。
解决此问题的可能方法：
- 拆分该查询多个较小的查询
- 在服务器上创建一个存储过程，在那里通过调用存储过程来替换查询的怪异
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
这篇关于视图中最大长度为8000错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
