sql server报错：将截断字符串或二进制数据

 

摘自：http://www.cnblogs.com/Sandheart/archive/2005/01/11/89996.html

 

运行一个SQL的时候：报错：将截断字符串或二进制数据

 

 

出现这种Exception，一般是由于数据类型长度造成的，例如：
数据库定义Field A varchar(50);
但在程序中定义对应Field varchar(100),并填满;
操作就会报错.

 

看来以上提示，自己再次检查函数：

 

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

 

 

 

 

 

 

 

 

--use chic
--select * from billstatus_tab
--select * from SigerBill
--select * from bill_tab
--select * from TrackBill
--go
--sp_helptext GetPostBillList
--GO
ALTER              FUNCTION GetAccAch(@TrackNetNO varchar(50)) 
RETURNS @GetAccAch TABLE 
( 
        [SaleNo] [varchar] (20) NULL, 
        [SaleDate] [datetime] NULL,  
        [AccountNo] [varchar] (20) NULL, 
        [AccountExec] [varchar] (30) NULL,
        [headship] [varchar] (20) NULL,--职务
        [EP_Rank] [varchar] (20) NULL, --级别
        [PostNetNO] [varchar] (20) NULL, 
        [PostNet] [varchar] (30) NULL, 
        [CustNo] [varchar] (50) NULL, 
        [CustName] [varchar] (50) NULL, 
        [CustType] [varchar] (20) NULL, 
        [FeeType] [varchar] (20) NULL,         
        [Fee] [decimal](18, 2) NULL ,
        [WeightRate] float,
        [AchRateFee] [decimal](18, 2) NULL ,
 [Inputer] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [InputNet] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
        [InputTime] [datetime] NULL, 
        [Remark] [varchar] (500) NULL,       //--未更改之前为50，而GetFee_tab 表里该字段为500，因此字段长度不够，造成出错

        [OrgRemark] [varchar] (50) NULL,
 [AddType] int,
 [ReceiptNo] [varchar] (20) NULL,
 [SupNameNo] [varchar] (20) NULL,
        [SupName] [varchar] (50) NULL,
 [DutyVolume] [decimal](18, 2) NULL,
        [OrderNo] [varchar] (50) NULL)
AS
BEGIN 
--Insert 销售业绩
INSERT INTO @GetAccAch(SaleNo,SaleDate,AccountNo,AccountExec,headship,EP_Rank,PostNetNO,PostNet,CustNo,CustName,CustType,FeeType,Fee,WeightRate,AchRateFee,
Inputer,InputNet,InputTime,Remark,OrgRemark,AddType,ReceiptNo,SupNameNo,SupName,DutyVolume,OrderNo)
SELECT G.SaleNo,G.GetFeeDate,G.ClerkNo,G.Clerk,E.headship,E.EP_Rank,G.SaleNetId,G.SaleNet,G.CustNo,G.CustName,G.CustType,GD.GFD_FeeType,GD.GFD_Price,GD.GFD_WeightRate,GD.GFD_Price*GD.GFD_WeightRate/100,
G.Inputer,G.InputNet,G.InputTime,G.Remark+GD.GFD_PurPro,'销售业绩',1,GD.GFD_ReceiptNo,E.EP_SupNameNo,E.EP_SupName,E.EP_DutyVolume,G.OrderNo
FROM GetFee_tab G inner join GetFeeDetail_tab GD on G.GetFeeID=GD.GFD_No left outer join Employee_tab E on E.UserNo=G.ClerkNo
Where G.Finance=1 /*and G.SaleNetID=@TrackNetNO*/ and (GD.GFD_FeeTypeNo=1 or GD.GFD_FeeTypeNo=4 or GD.GFD_FeeTypeNo=5) --1.充值服务费用，4。投资本金,5。其他费用

--Insert 销售充值卡业绩
INSERT INTO @GetAccAch(SaleNo,SaleDate,AccountNo,AccountExec,headship,EP_Rank,PostNetNO,PostNet,CustNo,CustName,CustType,FeeType,Fee,WeightRate,AchRateFee,
Inputer,InputNet,InputTime,Remark,OrgRemark,AddType,ReceiptNo,SupNameNo,SupName,DutyVolume,OrderNo)
SELECT S.SP_No,S.SP_Date,S.SP_ClerkNo,S.SP_Clerk,E.headship,E.EP_Rank,S.SP_NetId,S.SP_NetName,S.SP_CustNo,S.SP_CustName,S.SP_CustType,S.SP_SVType,S.SP_Price,S.SP_WeightRate,S.SP_Price*S.SP_WeightRate/100,
S.SP_Inputer,S.SP_InputNet,S.SP_InputTime,S.SP_Remark,'销售充值卡业绩',1,S.SP_ReceiptNo,E.EP_SupNameNo,E.EP_SupName,E.EP_DutyVolume,S.SP_OrderNo
FROM WtRechCard_Tab S left outer join Employee_tab E on E.UserNo=S.SP_ClerkNO
Where S.SP_Verify=1 /*and S.SC_NetId=@TrackNetNO*/ and (S.SP_SVTypeID=1 or S.SP_SVTypeID=4) --1.充值服务费用，4。投资本金

--Insert 销账业绩
INSERT INTO @GetAccAch(SaleNo,SaleDate,AccountNo,AccountExec,headship,EP_Rank,PostNetNO,PostNet,CustNo,CustName,CustType,FeeType,Fee,WeightRate,AchRateFee,
Inputer,InputNet,InputTime,Remark,OrgRemark,AddType,ReceiptNo,SupNameNo,SupName,DutyVolume)
SELECT C.CancelFeeID,C.CancelFeeDate,CT.ClerkNo,CT.Clerk,E.headship,E.EP_Rank,C.CancelFeeNetNO,C.CancelFeeNet,C.CustNo,C.CustName,C.CustType,C.PayExpt,C.CancelFee,C.WeightRate,C.CancelFee*C.WeightRate/100,
C.Inputer,C.InputNet,C.InputTime,C.Remark,'客户转账业绩',1,C.ReceiptNo,E.EP_SupNameNo,E.EP_SupName,E.EP_DutyVolume
FROM CancelFee_tab C inner join Customer_tab CT on C.CustNo=CT.CustNo left outer join Employee_tab E on E.UserNo=CT.ClerkNo left outer join Tab_SysParam T on T.ReferenceNo=503
Where C.Finance=1 and /*C.CancelFeeNetNO=@TrackNetNO and*/ C.PayExptNo=2 /*2.转入到充值服务费中*/ and (C.CancelFeeTypeID=2 or C.CancelFeeTypeID=3) --2.保证金 3预存款

--Insert 客户销账..业务来源于销售
INSERT INTO @GetAccAch(SaleNo,SaleDate,AccountNo,AccountExec,headship,EP_Rank,PostNetNO,PostNet,CustNo,CustName,CustType,FeeType,Fee,WeightRate,AchRateFee,
Inputer,InputNet,InputTime,Remark,OrgRemark,AddType,ReceiptNo,SupNameNo,SupName,DutyVolume,OrderNo)
SELECT C.CancelFeeID,C.CancelFeeDate,G.ClerkNo,G.Clerk,E.headship,E.EP_Rank,C.CancelFeeNetNO,C.CancelFeeNet,C.CustNo,C.CustName,C.CustType,C.PayExpt,CD.Price,100,CD.Price,
C.Inputer,C.InputNet,C.InputTime,C.Remark,'客户变更或终止服务-销售',2,CD.ReceiptNo,E.EP_SupNameNo,E.EP_SupName,E.EP_DutyVolume,G.OrderNo
FROM CancelFee_tab C inner join CancelFeeDetail CD on C.CancelFeeID=CD.CancelFeeID
inner join GetFeeDetail_tab GD on GD.GFD_ReceiptNo=CD.ReceiptNo inner join GetFee_tab G on G.GetFeeID=GD.GFD_No left outer join Employee_tab E on E.UserNo=G.ClerkNo
Where C.Finance=1 and /*C.CancelFeeNetNO=@TrackNetNO and*/ C.PayExptNo<>2 /*2.转入到充值服务费中*/ and (C.CancelFeeTypeID=1 or C.CancelFeeTypeID=4) --1.充值服务费用，4。投资本金

--Insert 客户销账..业务来源于充值
INSERT INTO @GetAccAch(SaleNo,SaleDate,AccountNo,AccountExec,headship,EP_Rank,PostNetNO,PostNet,CustNo,CustName,CustType,FeeType,Fee,WeightRate,AchRateFee,
Inputer,InputNet,InputTime,Remark,OrgRemark,AddType,ReceiptNo,SupNameNo,SupName,DutyVolume,OrderNo)
SELECT C.CancelFeeID,C.CancelFeeDate,S.SP_ClerkNo,S.SP_Clerk,E.headship,E.EP_Rank,C.CancelFeeNetNO,C.CancelFeeNet,C.CustNo,C.CustName,C.CustType,C.PayExpt,CD.Price,100,CD.Price,
C.Inputer,C.InputNet,C.InputTime,C.Remark,'客户变更或终止服务-充值卡',2,CD.ReceiptNo,E.EP_SupNameNo,E.EP_SupName,E.EP_DutyVolume,S.SP_OrderNo
FROM CancelFee_tab C inner join CancelFeeDetail CD on C.CancelFeeID=CD.CancelFeeID
inner join WtRechCard_Tab S on S.SP_ReceiptNo=CD.ReceiptNo left outer join Employee_tab E on E.UserNo=S.SP_ClerkNo
Where C.Finance=1 and /*C.CancelFeeNetNO=@TrackNetNO and*/ C.PayExptNo<>2 /*2.转入到充值服务费中*/ and (C.CancelFeeTypeID=1 or C.CancelFeeTypeID=4) --1.充值服务费用，4。投资本金

--Insert 客户销账..业务来源于系统转账
INSERT INTO @GetAccAch(SaleNo,SaleDate,AccountNo,AccountExec,headship,EP_Rank,PostNetNO,PostNet,CustNo,CustName,CustType,FeeType,Fee,WeightRate,AchRateFee,
Inputer,InputNet,InputTime,Remark,OrgRemark,AddType,ReceiptNo,SupNameNo,SupName,DutyVolume)
SELECT C.CancelFeeID,C.CancelFeeDate,CT.ClerkNo,CT.Clerk,E.headship,E.EP_Rank,C.CancelFeeNetNO,C.CancelFeeNet,C.CustNo,C.CustName,C.CustType,C.PayExpt,CD.Price,100,CD.Price,
C.Inputer,C.InputNet,C.InputTime,C.Remark,'客户变更或终止服务-系统转账',2,CD.ReceiptNo,E.EP_SupNameNo,E.EP_SupName,E.EP_DutyVolume
FROM CancelFee_tab C inner join CancelFeeDetail CD on C.CancelFeeID=CD.CancelFeeID
inner join CustAcc_tab CA on CA.CA_ReceiptNo=CD.ReceiptNo inner join Customer_tab CT on C.CustNo=CT.CustNo left outer join Employee_tab E on E.UserNo=CT.ClerkNo
Where C.Finance=1 and /*C.CancelFeeNetNO=@TrackNetNO and*/ C.PayExptNo<>2 /*2.转入到充值服务费中*/ and (C.CancelFeeTypeID=1 or C.CancelFeeTypeID=4) --1.充值服务费用，4。投资本金
 and CA.CA_OrgTypeNo>=5  --CA_OrgTypeNo>=5。。为系统转账产生的数据
  RETURN 
 
END   
 

 

 

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

 

 

 

 

 

解决方法：

将参数里的Remark字段加大长度即可。

        [Remark] [varchar] (500) NULL,       //--未更改之前为50，而GetFee_tab 表里该字段为500，因此字段长度不够，造成出错

评论

1 楼 njl_041x 2011-11-23  

楼主，我有个字段是varchar（max）程序里使用SqlDbType.Text。
当参数长度超19000后，就报这个错。低于这个字就可以。
请问应如何解决？？？

我的邮箱 [e-mail]