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 GOSET ANSI_NULLS OFF GO

 

 

 

 

 

 

 

 

--use chic--select * from billstatus_tab--select * from SigerBill--select * from bill_tab--select * from TrackBill--go--sp_helptext GetPostBillList--GOALTER              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)ASBEGIN  --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.OrderNoFROM GetFee_tab G inner join GetFeeDetail_tab GD on G.GetFeeID=GD.GFD_No left outer join Employee_tab E on E.UserNo=G.ClerkNoWhere 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_OrderNoFROM WtRechCard_Tab S left outer join Employee_tab E on E.UserNo=S.SP_ClerkNOWhere 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_DutyVolumeFROM 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=503Where 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.OrderNoFROM 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.ClerkNoWhere 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_OrderNoFROM 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_ClerkNoWhere 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_DutyVolumeFROM 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.ClerkNoWhere 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     

 

 

 

 

 

GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

 

 

 

 

 

 

解决方法：

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

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