Shanghai Mitsubishi Elevator Co., Ltd(上海三菱电梯有限公司) 的 Comprehensive Elevator Monitoring System (电梯综合监控系统) 程序由于需要处理的数据数据结构发生改变,导致无法使用高版本直接访问数据库,及进行编辑查看等操作。下面记录了一些尝试,已解决这个问题。
win10 系统在接入了 LonWorks TP/FT-10 USB 网卡后,使用其他版本 LonEle.exe以管理员身份运行访问时,可能会得到如下错误提示。
Alarm
---------------------------
System initialization error, the program will stop running!(4)
---------------------------
确定
---------------------------
或者说——
---------------------------
警告
---------------------------
系统初始化错误,程序将停止运行!(4)
---------------------------
确定
---------------------------
若以非管理员身份运行可能会提示“系统初始化错误,程序将停止运行!(6)”。
使用 SSMS 2005 等数据库管理工具,访问.\lonele2 本机数据库,以 SQL Server 身份验证方式(登录名 sa,密码为 lonele)。
使用 AdeptSQL Diff 等工具比较目标版本的表中不含数据数据库与源版本的数据库。可得
Difference summary, by object category
Category | Total items | Identical | Changed | Left-only | Right-only |
---|---|---|---|---|---|
Tables | 38 | 36 | 2 | 0 | 0 |
Views | 14 | 13 | 1 | 0 | 0 |
Procedures | 142 | 133 | 9 | 0 | 7 |
Principals | 2 | 1 | 1 | 0 | 0 |
File groups | 1 | 1 | 0 | 0 | 0 |
Tables 中 CardPublicInfo、CardSpecificInfo 表中列有增加。
Views 中 vCardInfo 中增加了一列。
Procedures 中 新增了 prGetPinfoNumberDLprogress、prGetPinfoNumberForDL、prSetAllCardSpecificInfoNumberDownloadSts、prSetCardInfoNumberDownloadSts、prSetCardSpecificInfoNumberDownloadSts、prUpdate_CardPublicInfoNumber、prUpdate_CardSpecificInfoNumberDownloadSts 共 7 个存储过程,及修改了 prInsertUpdate_CardPublicInfo、prInsertUpdate_CardSpecificInfo 共 2 个存储过程。
Principals 的差异则是 exec sp_grantdbaccess 'NT AUTHORITY\SYSTEM', 'dbo'; 变成了 exec sp_grantdbaccess 'sa', 'dbo';
数据库的修改顺序关系着能否将问题解决,经测试可先修改 CardSpecificInfo。需要先备份数据库,可进行完整备份BACKUP DATABASE lonele TO DISK = 'C:\lonele20180418_00.bak'
,接着执行修改表的操作。
修改 CardSpecificInfo 表及相应存储过程。
set xact_abort on;
go
begin transaction;
go
alter table CardSpecificInfo add
Number_DownloadSts tinyint not null constraint DFX_02459978 default(0);
go
set ANSI_NULLS on;
go
alter Procedure prGetCardSpecificInfo
/* Param List */
@intCard_ID int
AS
/******************************************************************************
** File: prGetCardSpecificInfo.sql
** Name: prGetCardSpecificInfo
** Desc: 获取指定卡号的专用信息
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** Card_ID[卡号]
**
** Auth: zjd
** Date: 2004/10/10
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SELECT *
FROM CardSpecificInfo
WHERE Card_ID = @intCard_ID
go
alter Procedure prDelete_CardSpecificInfo
/* Param List */
(@intCard_ID int,
@insCard_NodeID smallint)
AS
/******************************************************************************
** File: prDelete_CardSpecificInfo.sql
** Name: prDelete_CardSpecificInfo
** Desc: 删除指定卡的专用信息
**
** This template can be customized:
**
** Return values: ErrorCode (0 成功,非0 失败)
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** Card_ID[卡号]
** Card_NodeID[节点编号]
**
** Auth: zjd
** Date: 2004/09/08
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int,
@intTransactionCountOnEntry int,
@intRowCount int
SELECT @intErrorCode = @@ERROR
IF @intErrorCode = 0
BEGIN
SELECT @intTransactionCountOnEntry = @@TRANCOUNT
BEGIN TRANSACTION
END
IF @intErrorCode = 0
BEGIN
DELETE CardSpecificInfo
WHERE Card_ID = @intCard_ID AND Card_NodeID = @insCard_NodeID
SELECT @intRowCount = @@ROWCOUNT
SELECT @intErrorCode = @@ERROR
END
IF @@TRANCOUNT > @intTransactionCountOnEntry
BEGIN
IF @intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
-- 检查是否有一条记录被删除。
IF @intRowCount <> 1
RETURN 1
ELSE
RETURN @intErrorCode
go
alter Procedure prClearAllPinfo
/* Param List */
AS
/******************************************************************************
** File: prClearAllPinfo.sql
** Name: prClearAllPinfo
** Desc: remove all card information in cardpublicinfo and cardspecificinfo
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
**
** Auth: zjd
** Date: 2005/12/02
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int
-- TRUNCATE TABLE CardSpecificInfo
Delete CardSpecificInfo
-- TRUNCATE TABLE CardPublicInfo
Delete CardPublicInfo
SELECT @intErrorCode = @@ERROR
RETURN @intErrorCode
go
create Procedure prUpdate_CardSpecificInfoNumberDownloadSts
/* Param List */
(@insCard_NodeID smallint,
@inyDownloadSts tinyint)
AS
/******************************************************************************
** File: prUpdate_CardSpecificInfoDownloadSts.sql
** Name: prUpdate_CardSpecificInfoDownloadSts
** Desc:
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** NodeID[节点编号]
** DownloadSts[下载状态]
**
** Auth: zjd
** Date: 2005/12/07
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int,
@intTransactionCountOnEntry int,
@intRowCount int
SELECT @intErrorCode = @@ERROR
IF @intErrorCode = 0
BEGIN
SELECT @intTransactionCountOnEntry = @@TRANCOUNT
BEGIN TRANSACTION
END
IF @intErrorCode = 0
BEGIN
UPDATE CardSpecificInfo
SET Number_DownloadSts = @inyDownloadSts
WHERE Card_NodeID = @insCard_NodeID
SELECT @intRowCount = @@ROWCOUNT
SELECT @intErrorCode = @@ERROR
END
IF @@TRANCOUNT > @intTransactionCountOnEntry
BEGIN
IF @intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
-- 检查是否有一条记录被更新。
-- IF @intRowCount = 0
-- RETURN 1
-- ELSE
RETURN @intErrorCode
go
alter VIEW vCardInfo
AS
SELECT dbo.CardPublicInfo.*, dbo.CardSpecificInfo.Card_NodeID,
dbo.CardSpecificInfo.Card_AccessClassID, dbo.CardSpecificInfo.Card_Floors,
dbo.CardSpecificInfo.Card_DownloadSts,dbo.CardSpecificInfo.Number_DownloadSts
FROM dbo.CardPublicInfo INNER JOIN
dbo.CardSpecificInfo ON dbo.CardPublicInfo.Card_ID = dbo.CardSpecificInfo.Card_ID
go
create Procedure prSetCardSpecificInfoNumberDownloadSts
/* Param List */
(@insCard_ID int,
@insCard_NodeID smallint,
@inyDownloadSts tinyint)
AS
/******************************************************************************
** File: prSetCardSpecificInfoDownloadSts.sql
** Name: prSetCardSpecificInfoDownloadSts
** Desc:
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** Card_ID[卡号]
** Card_NodeID[节点编号]
** DownloadSts[下载状态]
**
** Auth: zjd
** Date: 2005/12/07
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int,
@intTransactionCountOnEntry int,
@intRowCount int
SELECT @intErrorCode = @@ERROR
IF @intErrorCode = 0
BEGIN
SELECT @intTransactionCountOnEntry = @@TRANCOUNT
BEGIN TRANSACTION
END
IF @intErrorCode = 0
BEGIN
UPDATE CardSpecificInfo
SET Number_DownloadSts = @inyDownloadSts
WHERE Card_ID = @insCard_ID AND Card_NodeID = @insCard_NodeID
SELECT @intRowCount = @@ROWCOUNT
SELECT @intErrorCode = @@ERROR
END
IF @@TRANCOUNT > @intTransactionCountOnEntry
BEGIN
IF @intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
-- 检查是否有一条记录被更新。
IF @intRowCount = 0
RETURN 1
ELSE
RETURN @intErrorCode
go
create Procedure prSetCardInfoNumberDownloadSts
/* Param List */
(@intCard_ID int,
@inyDownloadSts tinyint)
AS
/******************************************************************************
** File: prSetCardInfoDownloadSts.sql
** Name: prSetCardInfoDownloadSts
** Desc: 设置非接触卡信息下载标志(下载标志在CardSpecificInfo表中,因为是针对节点而言的)
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** Card_ID[卡号]
** DownloadSts[下载状态]
**
** Auth: zjd
** Date: 2004/12/01
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int,
@intTransactionCountOnEntry int,
@intRowCount int
SELECT @intErrorCode = @@ERROR
IF @intErrorCode = 0
BEGIN
SELECT @intTransactionCountOnEntry = @@TRANCOUNT
BEGIN TRANSACTION
END
IF @intErrorCode = 0
BEGIN
UPDATE CardSpecificInfo
SET Number_DownloadSts = @inyDownloadSts
WHERE Card_ID = @intCard_ID
SELECT @intRowCount = @@ROWCOUNT
SELECT @intErrorCode = @@ERROR
END
IF @@TRANCOUNT > @intTransactionCountOnEntry
BEGIN
IF @intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
RETURN @intErrorCode
go
create Procedure prSetAllCardSpecificInfoNumberDownloadSts
/* Param List */
@inyDownloadSts tinyint
AS
/******************************************************************************
** File: prSetAllCardSpecificInfoDownloadSts.sql
** Name: prSetAllCardSpecificInfoDownloadSts
** Desc:
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
**
** Auth: zjd
** Date: 2005/12/07
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int
UPDATE CardSpecificInfo
SET Number_DownloadSts = @inyDownloadSts
SELECT @intErrorCode = @@ERROR
RETURN @intErrorCode
go
alter Procedure prSetAllCardSpecificInfoDownloadSts
/* Param List */
@inyDownloadSts tinyint
AS
/******************************************************************************
** File: prSetAllCardSpecificInfoDownloadSts.sql
** Name: prSetAllCardSpecificInfoDownloadSts
** Desc:
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
**
** Auth: zjd
** Date: 2005/12/07
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int
UPDATE CardSpecificInfo
SET Card_DownloadSts = @inyDownloadSts
SELECT @intErrorCode = @@ERROR
RETURN @intErrorCode
go
alter Procedure prInsertUpdate_CardSpecificInfo
/* Param List */
(@intCard_ID int,
@insCard_NodeID smallint,
@inyAccessClassID tinyint,
@binFloors binary(8),
@inyDownloadSts tinyint,
@inyNumberDownloadSts tinyint)
AS
/******************************************************************************
** File: prInsertUpdate_CardSpecificInfo.sql
** Name: prInsertUpdate_CardSpecificInfo
** Desc: 更新非接触卡的私有信息,若无指定编号的记录,则添加一条。
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** Card_ID[卡号]
** Card_NodeID[节点编号]
** AccessClassID[通行等级编号]
** Floors[可去楼层]
** DownloadSts[下载状态]
**
** Auth: zjd
** Date: 2005/12/05
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int,
@intTransactionCountOnEntry int,
@intCount int,
@intRowCount int
-- 检查所需设置的通行等级是否被定义过
IF @inyAccessClassID <> 0
BEGIN
SELECT @intCount = (SELECT COUNT(AC_ID) FROM AccessClass WHERE AC_ID = @inyAccessClassID)
IF @intCount = 0
RETURN 1
END
SELECT @intCount = (SELECT COUNT(Card_ID) FROM CardSpecificInfo WHERE Card_ID = @intCard_ID AND Card_NodeID = @insCard_NodeID)
IF @intCount = 0
/* Insert CardSpecificInfo */
INSERT CardSpecificInfo(Card_ID,
Card_NodeID,
Card_AccessClassID,
Card_Floors,
Card_DownloadSts,
Number_DownloadSts)
VALUES (@intCard_ID,
@insCard_NodeID,
@inyAccessClassID,
@binFloors,
@inyDownloadSts,
@inyNumberDownloadSts)
ELSE
/* Update CardPublicInfo of Specified Card_ID */
UPDATE CardSpecificInfo
SET Card_AccessClassID = @inyAccessClassID,
Card_Floors = @binFloors,
Card_DownloadSts = @inyDownloadSts,
Number_DownloadSts=@inyNumberDownloadSts
WHERE Card_ID = @intCard_ID AND Card_NodeID = @insCard_NodeID
SELECT @intRowCount = @@ROWCOUNT
SELECT @intErrorCode = @@ERROR
-- 检查是否有一条记录被更新。
IF @intRowCount <> 1
RETURN 1
ELSE
RETURN @intErrorCode
go
alter Procedure prGetSInfoByNodeID
/* Param List */
@insNodeID smallint
AS
/******************************************************************************
** File: prGetSInfoByNodeID.sql
** Name: prGetSInfoByNodeID
** Desc: get all ncrcardrights of this node
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
**
** Auth: zjd
** Date: 2005/12/02
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SELECT *
FROM CardSpecificInfo
WHERE Card_NodeID = @insNodeID
go
alter Procedure prGetCardSpecificInfoDownloadSts
/* Param List */
@insNodeID smallint
AS
/******************************************************************************
** File: prGetCardSpecificInfoDownloadSts.sql
** Name: prGetCardSpecificInfoDownloadSts
** Desc: 获取指定NCR节点的个人信息下载状态
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** NodeID[节点编号]
**
** Auth: zjd
** Date: 2005/10/18
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SELECT *
FROM CardSpecificInfo
WHERE Card_NodeID = @insNodeID
go
alter table CardSpecificInfo drop
constraint DFX_02459978;
go
commit;
go
需要注意的是具体查询中以 DFX_ 为前缀后跟 8 位 16 进制的默认约束可能与上述不同。执行上述查询以修改表后,修改日期为 2019 年 3 月 28 日的LonELe.exe 程序就可以打开登录,进而访问相应的电梯卡等信息了。
当然也可以继续对数据库进行修改。
修改 CardPublicInfo 表及相应存储过程。
set xact_abort on;
go
begin transaction;
go
alter table CardPublicInfo add
Number smallint not null constraint DFX_03FA3B38 default(0),
Building_No smallint not null constraint DFX_03FA3B88 default(0);
go
set ANSI_NULLS on;
go
alter Procedure prGetCardPublicInfo
/* Param List */
@intCard_ID int
AS
/******************************************************************************
** File: prGetCardPublicInfo.sql
** Name: prGetCardPublicInfo
** Desc: 获取指定卡号的基本信息
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** Card_ID[卡号]
**
** Auth: zjd
** Date: 2004/10/09
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SELECT *
FROM CardPublicInfo
WHERE Card_ID = @intCard_ID
go
alter Procedure prGetAllPCardsExceptNode
/* Param List */
@insNode_ID smallint
AS
/******************************************************************************
** File: prGetAllPCardsExceptNode.sql
** Name: prGetAllPCardsExceptNode
** Desc: get all public information except the cards have been added to this node
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** NodeID[节点编号]
**
** Auth: zjd
** Date: 2005/12/01
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SELECT *
FROM CardPublicInfo
WHERE Card_ID NOT IN (SELECT Card_ID FROM CardSpecificInfo WHERE Card_NodeID = @insNode_ID)
go
alter Procedure prGetAllPCards
/* Param List */
AS
/******************************************************************************
** File: prGetAllPCards.sql
** Name: prGetAllPCards
** Desc: get public infomation of all cards
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
**
** Auth: zjd
** Date: 2005/12/01
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SELECT *
FROM CardPublicInfo
go
alter Procedure prDelete_CardPublicInfo
/* Param List */
@intCard_ID int
AS
/******************************************************************************
** File: prDelete_CardPublicInfo.sql
** Name: prDelete_CardPublicInfo
** Desc: 删除指定卡的公用信息
**
** This template can be customized:
**
** Return values: ErrorCode (0 成功,非0 失败)
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** Card_ID[卡号]
**
** Auth: zjd
** Date: 2004/09/08
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int,
@intTransactionCountOnEntry int,
@intRowCount int
SELECT @intErrorCode = @@ERROR
IF @intErrorCode = 0
BEGIN
SELECT @intTransactionCountOnEntry = @@TRANCOUNT
BEGIN TRANSACTION
END
IF @intErrorCode = 0
BEGIN
DELETE CardPublicInfo
WHERE Card_ID = @intCard_ID
SELECT @intRowCount = @@ROWCOUNT
SELECT @intErrorCode = @@ERROR
END
IF @@TRANCOUNT > @intTransactionCountOnEntry
BEGIN
IF @intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
-- 检查是否有一条记录被删除。
IF @intRowCount <> 1
RETURN 1
ELSE
RETURN @intErrorCode
go
alter Procedure prClearAllPinfo
/* Param List */
AS
/******************************************************************************
** File: prClearAllPinfo.sql
** Name: prClearAllPinfo
** Desc: remove all card information in cardpublicinfo and cardspecificinfo
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
**
** Auth: zjd
** Date: 2005/12/02
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int
-- TRUNCATE TABLE CardSpecificInfo
Delete CardSpecificInfo
-- TRUNCATE TABLE CardPublicInfo
Delete CardPublicInfo
SELECT @intErrorCode = @@ERROR
RETURN @intErrorCode
go
alter VIEW vCardInfo
AS
SELECT dbo.CardPublicInfo.*, dbo.CardSpecificInfo.Card_NodeID,
dbo.CardSpecificInfo.Card_AccessClassID, dbo.CardSpecificInfo.Card_Floors,
dbo.CardSpecificInfo.Card_DownloadSts,dbo.CardSpecificInfo.Number_DownloadSts
FROM dbo.CardPublicInfo INNER JOIN
dbo.CardSpecificInfo ON dbo.CardPublicInfo.Card_ID = dbo.CardSpecificInfo.Card_ID
go
alter Procedure prInsertUpdate_CardPublicInfo
/* Param List */
(@intCard_ID int,
@inyPublicshTimes tinyint,
@inyAccidentMark tinyint,
@dtsBeginDate smalldatetime,
@dtsEndDate smalldatetime,
@chvnOwnerName nvarchar(30),
@chvnOwnerInfo nvarchar(60),
@inyDeleted tinyint,
@insNumber smallint,
@insBuildingNo smallint)
AS
/******************************************************************************
** File: prInsertUpdate_CardPublicInfo.sql
** Name: prInsertUpdate_CardPublicInfo
** Desc: 更新非接触卡的公共信息,若无指定编号的记录,则添加一条。
**
** This template can be customized:
**
** Return values: ErrorCode (0 成功,非0 失败)
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** Card_ID[卡号]
** PublicshTimes[发行次数]
** AccidentMark[事故标记]
** BeginDate[有效开始时间]
** EndDate[有效结束时间]
** OwnerName[持卡人姓名]
** OwnerInfo[持卡人信息]
** Deleted[是否被删除]
**
** Auth: zjd
** Date: 2004/12/01
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int,
@intTransactionCountOnEntry int,
@intCount int,
@intRowCount int
SELECT @intErrorCode = @@ERROR
IF @intErrorCode = 0
BEGIN
SELECT @intTransactionCountOnEntry = @@TRANCOUNT
BEGIN TRANSACTION
END
IF @intErrorCode = 0
BEGIN
SELECT @intCount = (SELECT COUNT(Card_ID) FROM CardPublicInfo WHERE Card_ID = @intCard_ID)
SELECT @intErrorCode = @@ERROR
END
IF @intErrorCode = 0
BEGIN
IF @intCount = 0
/* Insert CardPublicInfo */
INSERT CardPublicInfo(Card_ID,
Card_PublishTimes,
Card_AccidentMark,
Card_BeginDate,
Card_EndDate,
Card_OwnerName,
Card_OwnerInfo,
Card_Deleted,
Number,
Building_No)
VALUES (@intCard_ID,
@inyPublicshTimes,
@inyAccidentMark,
@dtsBeginDate,
@dtsEndDate,
@chvnOwnerName,
@chvnOwnerInfo,
@inyDeleted,
@insNumber,
@insBuildingNo)
ELSE
/* Update CardPublicInfo of Specified Card_ID */
UPDATE CardPublicInfo
SET Card_PublishTimes = @inyPublicshTimes,
Card_AccidentMark = @inyAccidentMark,
Card_BeginDate = @dtsBeginDate,
Card_EndDate = @dtsEndDate,
Card_OwnerName = @chvnOwnerName,
Card_OwnerInfo = @chvnOwnerInfo,
Card_Deleted = @inyDeleted,
Number=@insNumber,
Building_No=@insBuildingNo
WHERE Card_ID = @intCard_ID
SELECT @intRowCount = @@ROWCOUNT
SELECT @intErrorCode = @@ERROR
END
IF @@TRANCOUNT > @intTransactionCountOnEntry
BEGIN
IF @intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
-- 检查是否有一条故障记录被更新或被插入。
IF @intRowCount <> 1
RETURN 1
ELSE
RETURN @intErrorCode
go
alter table CardPublicInfo drop
constraint DFX_03FA3B88;
go
create Procedure prUpdate_CardPublicInfoNumber
/* Param List */
(@intCard_ID int,
@insNumber smallint)
AS
/******************************************************************************
** File: prUpdate_EleSet.sql
** Name: prUpdate_EleSet
** Desc: 更改电梯组名称和组用途
**
** This template can be customized:
**
** Return values: ErrorCode (0 成功,非0 失败)
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** Set_ID[组编号]
** Name[组名称]
** Usage[组用途]
**
** Auth: zjd
** Date: 2004/08/30
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
** 2013/11/15 zjd Add column Set_Usage
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int,
@intTransactionCountOnEntry int,
@intRowCount int
SELECT @intErrorCode = @@ERROR
IF @intErrorCode = 0
BEGIN
SELECT @intTransactionCountOnEntry = @@TRANCOUNT
BEGIN TRANSACTION
END
IF @intErrorCode = 0
BEGIN
UPDATE CardPublicInfo
SET Number = @insNumber
WHERE Card_ID = @intCard_ID
SELECT @intRowCount = @@ROWCOUNT
SELECT @intErrorCode = @@ERROR
END
IF @@TRANCOUNT > @intTransactionCountOnEntry
BEGIN
IF @intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
-- 检查是否有一条记录被更新。
IF @intRowCount <> 1
RETURN 1
ELSE
RETURN @intErrorCode
go
alter table CardPublicInfo drop
constraint DFX_03FA3B38;
go
commit;
go
还剩两个存储过程 Procedure prGetPinfoNumberDLprogress、prGetPinfoNumberForDL 有待创建。
创建 Procedure prGetPinfoNumberDLprogress 存储过程。
set xact_abort on;
go
begin transaction;
go
set ANSI_NULLS on;
go
create Procedure prGetPinfoNumberDLprogress
/* Param List */
(@inyProgress as tinyint = 0 OUTPUT,
@insNodeID as smallint,
@inyDownloadSts as tinyint)
AS
/******************************************************************************
** File: prGetPinfoDLprogress.sql
** Name: prGetPinfoDLprogress
** Desc: Get public infomation download progress.
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
**
** Auth: zjd
** Date: 2006/03/02
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
SET NOCOUNT ON
DECLARE @intErrorCode int,
@intTotalNum int,
@intTransactionCountOnEntry int,
@intCurrentNum int
SELECT @intErrorCode = @@ERROR
IF @intErrorCode = 0
BEGIN
SELECT @intTransactionCountOnEntry = @@TRANCOUNT
BEGIN TRANSACTION
END
IF @intErrorCode = 0
BEGIN
SELECT @intTotalNum = (SELECT COUNT(*) FROM vCardInfo WHERE Card_NodeID = @insNodeID)
SELECT @intCurrentNum = (SELECT COUNT(*) FROM vCardInfo WHERE Card_NodeID = @insNodeID AND Number_DownloadSts = @inyDownloadSts)
SELECT @inyProgress = ROUND((@intCurrentNum*100)/@intTotalNum,2)
SELECT @intErrorCode = @@ERROR
END
IF @@TRANCOUNT > @intTransactionCountOnEntry
BEGIN
IF @intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
RETURN @intErrorCode
go
commit;
go
创建 prGetPinfoNumberForDL 存储过程
set xact_abort on;
go
begin transaction;
go
set ANSI_NULLS on;
go
create Procedure prGetPinfoNumberForDL
/* Param List */
(@insNodeID as smallint,
@inyDownloadSts as tinyint,
@inyNumberDownloadSts as tinyint,
@BatchSize as integer = 2)
AS
/******************************************************************************
** File: prGetPinfoForDL.sql
** Name: prGetPinfoForDL
** Desc:
**
** This template can be customized:
**
** Return values:
**
** Called by:
**
** Parameters:
** Input Output
** ---------- -----------
** NodeID[节点编号]
** DownloadSts[下载标记]
** BatchSize[要获取的记录数]
**
** Auth: zjd
** Date: 2005/12/07
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*******************************************************************************/
set CONCAT_NULL_YIELDS_NULL OFF
-- set nocount on
declare @chvSelect varchar(8000),
@chvFrom varchar(8000),
@chvWhere varchar(8000),
@chvOrderby varchar(8000),
@chvSQL varchar(8000)
set @chvSelect = 'SELECT top ' + Convert(varchar, @BatchSize)
+ ' * '
set @chvFrom = ' FROM vCardInfo '
set @chvWhere = ' WHERE Card_NodeID = ' + Convert(varchar,@insNodeID) + ' AND Card_DownloadSts = ' + Convert(varchar,@inyDownloadSts)+' AND Number_DownloadSts = ' + Convert(varchar,@inyNumberDownloadSts)
set @chvOrderBy = ' ORDER BY Card_ID ASC'
set @chvSQL = @chvSelect + @chvFrom + @chvWhere + @chvOrderBy
exec (@chvSQL)
go
commit;
go
在电梯综合监控程序的登录界面上,如果忘记了管理程序的登录密码,可以按住 ctrl + shift 在空白处单击 3-4次,输入操作员账号 smec,密码为 200236。