一个以前的系统,一个很老的BUG
流程大概是这个样子,用一个Windows服务监控一个txt文件,内容发生改变时启动一个程序。现在这个文件的容量有11M多。在程序中读入文件转换成XML格式的字符串,传进数据库,调用sp_xml_preparedocument处理传入的XML数据,然后用游标遍历,先做两个嵌套的IF判断,如果符合条件,则更新数据库中的数据。
现在的问题是,只有部分数据得到了更新。我做了三次测验,更新的记录条数分别是:245,266,229,其中还有满足条件的记录三次都没有得到更新,而我用直接调用执行更新的脚本传入刚才那条记录,记录更新成功。
实在没辙了,想到会不会是数据量太大,在执行的过程中产生了数据遗漏?以前没碰到过,向各位大大求个建议或者思路 : )
流程大概是这个样子,用一个Windows服务监控一个txt文件,内容发生改变时启动一个程序。现在这个文件的容量有11M多。在程序中读入文件转换成XML格式的字符串,传进数据库,调用sp_xml_preparedocument处理传入的XML数据,然后用游标遍历,先做两个嵌套的IF判断,如果符合条件,则更新数据库中的数据。
现在的问题是,只有部分数据得到了更新。我做了三次测验,更新的记录条数分别是:245,266,229,其中还有满足条件的记录三次都没有得到更新,而我用直接调用执行更新的脚本传入刚才那条记录,记录更新成功。
实在没辙了,想到会不会是数据量太大,在执行的过程中产生了数据遗漏?以前没碰到过,向各位大大求个建议或者思路 : )
//这个是脚本
CREATE PROCEDURE [dbo].[uspInsertSynchronizedData]
(
@sXMLDoc TEXT= NULL
)
ASdeclare @id int,
@wNo varchar(10),
@pCode varchar(10),
@sLoc varchar(10),
@sSku varchar(10),
@junk varchar(50),
@others varchar(50),
@OnHandQty int,
@UnitFactor int,
@PickLocation varchar(10),
@filter varchar(19),
@ImportCertNumber varchar(50),
@CountryOfOrigin varchar(3),
@CommittedQty intdeclare @StoreId int
declare @ProductId int
declare @InventoryId int
declare @today datetime
declare @PRODUCT_ERROR varchar(100)
declare @INVENTORYID_ERROR varchar(100)
declare @user varchar(20)
DECLARE @nDoc INT SET NOCOUNT ON
set @PRODUCT_ERROR=' have not the corresponding ProductID in the local Store Master.'
set @INVENTORYID_ERROR=' the Inventory id has been existed please check it but it has been updated.'
set @user='InventoryService' CREATE TABLE [#SyncErrorLog] (
[id] [int] NULL ,
[wNo] [varchar] (10) NULL ,
[pCode] [varchar] (10) NULL ,
[sLoc] [varchar] (10) NULL ,
[sSku] [varchar] (10) NULL ,
[OnHandQty] [int] NULL ,
[UnitFactor] [int] NULL ,
[PickLocation] [varchar] (10) NULL ,
[filter] [varchar] (19) NULL,
[ImportCertNumber] [varchar] (50) NULL,
[CountryOfOrigin] [varchar](3) NULL,
[ErrorDesc] [varchar](100) NULL
) ON [PRIMARY] CREATE TABLE [#SyncData] (
[wNo] [varchar] (10) NULL ,
[pCode] [varchar] (10) NULL ,
[sLoc] [varchar] (10) NULL ,
[sSku] [varchar] (10) NULL ,
[OnHandQty] [int] NULL ,
[UnitFactor] [int] NULL ,
[PickLocation] [varchar] (10) NULL ,
[filter] [varchar] (19) NULL,
[ImportCertNumber] [varchar] (50) NULL,
[CountryOfOrigin] [varchar](3) NULL,
[id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
EXEC sp_xml_preparedocument @nDoc OUTPUT, @sXMLDoc
insert into #SyncData(wNo,pCode,sLoc,sSku,OnHandQty,UnitFactor,PickLocation,filter,ImportCertNumber,CountryOfOrigin)
select *
from OPENXML (@nDoc,'/InventoryItems/InventoryItem',1)
with (
WarehouseNo varchar(10) ,
ProductCode varchar(10) ,
SubIntentoryLocation varchar(10) ,
StockSKU varchar(10) ,
OnHandQty int ,
UnitFactor int ,
PickLocation varchar(10) ,
filter varchar(19) ,
ImportCertNumber varchar(50) ,
CountryOfOrigin varchar(3)
) a set @today=dbo.ufGetGMTDate(getdate())--begin trandeclare sync_cur cursor for
select * from #SyncData
for read onlyopen sync_cur fetch sync_cur into @wNo,@pCode,@sLoc,@sSku,@OnHandQty,@UnitFactor,@PickLocation,@filter,@ImportCertNumber,@CountryOfOrigin,@id
while @@fetch_status=0
begin
-- To see whether the StoreID in Store Master
if exists(select * from store where warehouseid=@wNo)
begin
-- get storeid
select @StoreId=StoreId from store where warehouseid=@wNo
-- To See whether the ProductId in Product Master
if exists(select * from product where ProductSKU=@pCode)
begin
-- get productid
select @ProductId=ProductId from product where ProductSKU=@pCode
-- handle product inventory data
if exists(select * from ProductInventory where StockSKU=@sSKU and SubInventoryLocation=@sLoc and IsDeleted=0) begin
update ProductInventory
set OnHandQty=@OnHandQty ,UpdatedBy=@user,UpdatedOn=@today
where StockSKU=@sSKU and SubInventoryLocation=@sLoc and IsDeleted=0
end
else
begin
insert into ProductInventory(SubInventoryLocation,StockSKU,OnHandQty,CommittedQty,CreatedOn,CreatedBy,IsDeleted)
values(@sLoc,@sSKU,@OnHandQty,0,@today,@user,0)
end
-- get the inventory id new or old one
select @InventoryId=InventoryId
from ProductInventory
where StockSKU=@sSKU and SubInventoryLocation=@sLoc and IsDeleted=0
-- handle product inventory xref data
if not exists(select * from ProductInventoryXref where StoreId=@StoreId and ProductId=@ProductId and IsDeleted=0)
begin
insert into ProductInventoryXref(InventoryId,ProductId,StoreId,CreatedOn,CreatedBy,UnitFactor,IsDeleted)
values(@InventoryID,@ProductId,@StoreId,@today,@user,1,0)
end -- handle error data
if not exists(select * from ProductInventoryXref where StoreId=@StoreId and ProductId=@ProductId and InventoryId=@InventoryId and IsDeleted=0)
begin
insert into #SyncErrorLog
values(@id,@wNo,@pCode,@sLoc,@sSku,@OnHandQty,@UnitFactor,@PickLocation,@filter,@ImportCertNumber,@CountryOfOrigin,@INVENTORYID_ERROR) -- new added by Jerry Woo 2005-10-20 for changing sublocation
select @CommittedQty=CommittedQty
from ProductInventory p inner join ProductInventoryXref pix on p.InventoryID=pix.InventoryID
where StoreId=@StoreId and ProductId=@ProductId and pix.IsDeleted=0 and p.IsDeleted=0 update ProductInventoryXref
set InventoryID=@InventoryId
where StoreId=@StoreId and ProductId=@ProductId and IsDeleted=0 update ProductInventory
set CommittedQty=@CommittedQty
where InventoryId=@InventoryId
end
-- when unitfactor not null update it
if (@UnitFactor is not null)
begin
update ProductInventoryXref
set UnitFactor=@UnitFactor,UpdatedBy=@user,UpdatedOn=@today
where StoreId=@StoreId and ProductId=@ProductId and InventoryId=@InventoryId and IsDeleted=0
end
-- when PickLocation not null update it
if (@PickLocation is not null)
begin
update ProductInventoryXref
set PickLocation=@PickLocation,UpdatedBy=@user,UpdatedOn=@today
where StoreId=@StoreId and ProductId=@ProductId and InventoryId=@InventoryId and IsDeleted=0
end
end
else
begin
-- log error data
insert into #SyncErrorLog
values(@id,@wNo,@pCode,@sLoc,@sSku,@OnHandQty,@UnitFactor,@PickLocation,@filter,@ImportCertNumber,@CountryOfOrigin,@PRODUCT_ERROR)
end
end fetch sync_cur into @wNo,@pCode,@sLoc,@sSku,@OnHandQty,@UnitFactor,@PickLocation,@filter,@ImportCertNumber,@CountryOfOrigin,@id
end--commit tranclose sync_cur
deallocate sync_cur
select * from #SyncErrorLog
--select * from #syncDataEXEC sp_xml_removedocument @nDoc
drop table #SyncData
drop table #SyncErrorLog
//这个文件内容发生改变时的事件
/// <summary>
/// Specify what is done when a file is changed or created.
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
private void OnChanged(object source, FileSystemEventArgs e)
{
this.CreateLoggingPath(); string fileName = e.FullPath;
string changeTpye = e.ChangeType.ToString();
this.writeLog(fileName + " " + changeTpye);
bool succeed = false;
succeed = this.LoadFile(fileName);
this.writeLog("after load file");
if(succeed)
{
this.writeLog("File Loading Succeed");
succeed = false;
try
{
loadFileCtrl = new InventorySynchronizeCtrl();
succeed = loadFileCtrl.LoadFileByStoreProcedure(fileName,LOGFILE);
}
catch(Exception e1)
{
string loadError = e1.ToString();
this.writeLog(loadError);
} this.writeLog("after load file into database"); if(succeed)
{
this.writeLog("Inventory Synchronize Succeed");
}
else
{
this.writeLog("Inventory Synchronize Failed");
} }
else
{
this.writeLog("File Loading Failed");
}
this.loggingCreated = false;
}
//这是刚才那个事件调用的方法
/// <summary>
/// A method to read the inventory file and totaly use store procedure to affect the database
/// </summary>
/// <param name="fileName">The name of synchronized file from Oracle System</param>
/// <param name="log">The name of log file</param>
/// <returns>whether succeed</returns>
[AutoComplete]
public bool LoadFileByStoreProcedure(string fileName , string logFileName)
{
bool result = false;
StringBuilder sb = new StringBuilder();
XmlTextWriter xr = new XmlTextWriter(new StringWriter(sb));
FileSynchronizeVO lineContent = new FileSynchronizeVO();
DataSet errorDS = null; _logFile = logFileName; WriteLog("in load file to database" + _logFile); try
{
_streamRead = new StreamReader(fileName,System.Text.Encoding.ASCII);
//xr.Formatting = Formatting.Indented;
xr.WriteStartElement("InventoryItems");
while(GetLineContentFromFile(out lineContent))
{
if(lineContent.dataValid)
{
WriteXmlNode(xr, lineContent);
}
}
xr.WriteEndElement();
errorDS = InventorySynchronizeProcess.InsertSynchronizedData(sb.ToString());
result = true;
}
catch(Exception e)
{
string loadError = e.ToString();
WriteLog(loadError);
throw;
}
finally
{
_streamRead.Close();
xr.Close();
WriteErrorLog(errorDS, fileName);
}
return result;
}
//继续
public static DataSet InsertSynchronizedData(string xml)
{
DataSet ds = null;
try
{
using(ConfiguredDataCommand dataCommand = ConfiguredItems.GetDataCommand(CommandPrefix.OrderManagement , CommandName.InsertSynchronizedData))
{
dataCommand.SetParameterValue(CommandParam.XmlDoc , xml); ds = dataCommand.ExecuteDataset();
} }
catch (AppException)
{
throw ;
}
catch (Exception ex)
{
throw new SysException(ex.Message);
}
return ds;
}
succeed = loadFileCtrl.LoadFileByStoreProcedure(fileName,LOGFILE);
关键是上面的调用。
InventorySynchronizeCtrl返回什么,代码是?
//存储过程在这里
<?xml version="1.0" encoding="utf-8" ?>
<DataAccessConfiguration>
<Commands>
<DataCommand Name="OrderManagement.InsertSynchronizedData" HelperAlias="SqlHelper" CommandType="StoredProcedure" CommandTimeout="30">
<CommandText>uspInsertSynchronizedData</CommandText>
<Parameters>
<Parameter Alias="XmlDoc" ParameterName="@sXmlDoc" DbType="String" Direction="Input" />
</Parameters>
<ConnectionString Value="Server=*.*.*.*;UID=sa;PWD=sa;Database=*****;"/>
</DataCommand>
</Commands>
</DataAccessConfiguration>
{
}
下面是关键的调用方法:
生成xml文档后,xml数据在sb中,断点调试看看,每次sb都能读取到所有的数据吗?
xr.WriteEndElement();
errorDS = InventorySynchronizeProcess.InsertSynchronizedData(sb.ToString());
result = true;另外errorDS 好像只是为了记录错误的信息,还是记录错误的数据?
如果是错误的信息,我觉得不应该让InsertSynchronizedData方法,返回数据集,返回一个错误信息就好了。
所以不是很明白为什么要调用返回数据集的方法:
ds = dataCommand.ExecuteDataset();
//脚本里面的,原来开发的人写的
CREATE TABLE [#SyncErrorLog] (
[id] [int] NULL ,
[wNo] [varchar] (10) NULL ,
[pCode] [varchar] (10) NULL ,
[sLoc] [varchar] (10) NULL ,
[sSku] [varchar] (10) NULL ,
[OnHandQty] [int] NULL ,
[UnitFactor] [int] NULL ,
[PickLocation] [varchar] (10) NULL ,
[filter] [varchar] (19) NULL,
[ImportCertNumber] [varchar] (50) NULL,
[CountryOfOrigin] [varchar](3) NULL,
[ErrorDesc] [varchar](100) NULL
) ON [PRIMARY]--自建的一个表,发生异常时插入错误信息的,因为是循环,所以用了table.
--返回DataSet的原因,应该底层(我看不到了)里面操作数据库的方法
insert into #SyncErrorLog
values(@id,@wNo,@pCode,@sLoc,@sSku,@OnHandQty,@UnitFactor,@PickLocation,@filter,@ImportCertNumber,@CountryOfOrigin,@PRODUCT_ERROR)
第二个问题不确定,他是把xml数据(在格式化成xml以前就有11M多)一次性传入数据库的。
还有一个问题,我检查了他的APPLOG,里面报了一个这个异常: nested Exception 。不知道是什么原因?我google了好久,没明白这个异常的原因.
折磨了我两天了,这个bug已经存在至少两年了,难道注定就无解了吗?
还有一个问题,我检查了他的APPLOG,里面报了一个这个异常: nested Exception 。不知道是什么原因?我 google了好久,没明白这个异常的原因.原来的人,够晕的,这样的错误记录了有什么用?
catch(Exception e1)
{
string loadError = e1.ToString();
this.writeLog(loadError);
}
这样记录的就是错误的类型名称!
修改一下,再执行会发现什么错误,贴出来!
catch(Exception e1)
{
//把错误源和错误发生时的堆栈信息都记录到日志
string loadError = e1.Message+"\r\n"+e1.StackTrace;
this.writeLog(loadError);
}
但是既然又数据可以执行成功,说明封装方法出现问题的机率不大,最有可能就是游标循环那块有些代码不代严谨。你好好研究下那些sql语句。前面使用sp_xml_preparedocument我们倒是用过。我还能看明白。后面一堆游标的东西我就不太熟了,看到累。
你在执行的时候,打开sqlserver的事件查看器,调试一下。
查到传进来的字符串后
你直接拿传进来的xml串到查询分析器中执行,多执行几遍。看看返回的数据是不是一样的。
还有就是要学会调试sql语句。
一个最简单的办法就是一步步的剪切,就是去掉你怀疑的部分,留下可以执行的部分,当然要保证正确。
还要就是要多写写select,方便查看各个变量的值。
我想只要你这么做的,应该能查出什么问题的。
如果存储过程没问题,那只能找你们公司要封装方法的源代码 了。
找到了,用自己的DBHelper跑了一遍,报了个这个:--超时时间已到。在操作完成之前超时时间已过或服务器未响应。公司封装的底层,把这个吃掉了,只返回了个:--6/16/2010 10:36:51 AM--*******.Business.Common.SysException: An exception occurred while using an AdoHelper object
at *********.Business.OM.Inventory.Process.InventorySynchronizeProcess.InsertSynchronizedData(String xml)
at *********.Business.OM.Inventory.Controller.InventorySynchronizeCtrl.LoadFileByStoreProcedure(String fileName, String logFileName)
把cmd.CommandTimeout设置成300后,记录更新成功数为6083,是原来的30倍了。最后,听老大的直接改成999999了,异常没了,成功更新记录17106条。谢谢大家了,数据库这一块只会写个增删改查,其他的还得再好好学学了。