【问】下传盘点机3700+ 报错，提示“可能存在非法或无效数据”，如何解决？
【适用版本】3700盘点机	【模块】基础资料	【点击数】209
【知识编号】H2017101200001
【问题描述】专卖9  下传盘点机3700+ 报错，错误12  可能存在非法或无效数据， 错误信息12

【答】 3700盘点机数据检测，如存在如下特殊字符是下载不了数据到盘点机的请备份总部的数据库。执行如下语句
Use isszmv9—先备份数据库再执行以下语
go
–处理：自编码为0的改为和货号一样，
select * from t_bd_item_info where item_subno =’0′
update t_bd_item_info set item_subno=left(item_no ,15)
where item_subno =’0′ or item_subno = ” or item_subno is null
go
–处理单位+规格长度大于13的数据
UPDATE t_bd_item_info SET unit_no = ”,item_size = ” WHERE datalength(unit_no + item_size) >13
go
–处理附加条码与货号一致的数据
DELETE t_bd_item_barcode WHERE item_no = item_barcode
go
–处理特殊字符
update t_bd_item_info set item_name=left(item_name,len(item_name)-1) where ascii(right(item_name,1))=45
go
update t_bd_item_info
 set item_name = replace(replace(item_name,char(10),”),char(13),”)
where charindex(char(10),item_name) > 0 or charindex(char(13),item_name) > 0
go–处理：去掉规格,名称,简称里,含有特殊字符（,）,！,｛,。,
update t_bd_item_info  set item_size   =  replace (item_size,'(‘,'(‘) where item_size    like’%（%’
update t_bd_item_info  set item_name   =  replace (item_name,'(‘,'(‘)   where item_name    like’%（%’
update t_bd_item_info  set item_name   =  replace (item_name,’)’,’)’)   where item_name    like’%）%’
update t_bd_item_info  set item_name   =  replace (item_name,’！’,”)   where item_name    like’%！%’
update t_bd_item_info  set item_subname=  replace (item_subname,’！’,”)where item_subname like’%！%’
update t_bd_item_info  set item_name   =  replace (item_name,’：’,”)   where item_name    like’%：%’
update t_bd_item_info  set item_subname=  replace (item_subname,’：’,”)where item_subname like’%：%’
update t_bd_item_info  set item_name   =  replace (item_name,’｛’,”)   where item_name    like’%｛%’
update t_bd_item_info  set item_subname=  replace (item_subname,’｛’,”)where item_subname like’%｛%’
update t_bd_item_info  set item_name   =  replace (item_name,’，’,”)   where item_name    like’%，%’
update t_bd_item_info  set item_subname=  replace (item_subname,’，’,”)where item_subname like’%，%’
update t_bd_item_info  set item_name   =  replace (item_name,’＃’,”)   where item_name    like’%＃%’
update t_bd_item_info  set item_subname=  replace (item_subname,’＃’,”)where item_subname like’%＃%’
update t_bd_item_info  set item_name   =  replace (item_name,’。’,”)   where item_name    like’%。%’
update t_bd_item_info  set item_subname=  replace (item_subname,’。’,”)where item_subname like’%。%’
go
–处理：将全角转换成半角。
–步骤1：
if object_id(N’u_convert’,N’FN’) is not null
drop   function u_convert
GO
create   function   u_convert(
@str   nvarchar(4000),   
@flag   bit            
)
returns   nvarchar(4000)
AS
begin
    declare 
          @pat nvarchar(8),
          @step   int,
          @i   int,
          @spc   int
    if @flag=0
     begin
       select   @pat=N’%[！-～]%’,@step=-65248,
       @str=replace(@str,N’　’,N’   ‘)
     end
    else
     begin
       select   @pat=N’%[!-~]%’,@step=65248,
       @str=replace(@str,N’   ‘,N’　’)
     end
    set   @i=patindex(@pat   collate LATIN1_GENERAL_BIN,@str)
    while   @i>0
       select   @str=replace(@str,
    substring(
               @str,@i,1),
               nchar(unicode(substring(@str,@i,1))+@step)),
               @i=patindex(@pat   collate   LATIN1_GENERAL_BIN,@str)
     return(@str)
end
GO
—————————
–步骤2：修改全角调用函数
alter table t_bd_item_info disable trigger all
update t_bd_item_info set item_size= dbo.u_convert(item_size,0) 
update t_bd_item_info set item_name= dbo.u_convert(item_name,0)
update t_bd_item_info set item_subname= dbo.u_convert(item_subname,0)
update t_bd_item_info set item_no= dbo.u_convert(item_no,0) 
update t_bd_item_info set item_subno= dbo.u_convert(item_subno,0)  
update t_bd_item_info set item_clsno= dbo.u_convert(item_clsno,0)  
update t_pc_branch_price set item_no= dbo.u_convert(item_no,0) 
update t_bd_item_barcode set item_no= dbo.u_convert(item_no,0) 
alter table t_bd_item_info enable trigger all