Intouch到SQL Server与Excel报表链路实战：SCADA数据落地与恢复

简介这份文档面向SCADA系统工程师与Intouch组态开发人员聚焦WonderWare Intouch 2014R2平台与SQL Server 2012数据库的集成及Excel报表系统搭建帮助解决工业现场实时数据存储、历史数据查询与报表输出的实际问题。资源包共1个doc文件约2.48MB内容以图文步骤形式展开涵盖数据库配置、权限设置、标记名字典建立、SQL访问管理器绑定、SQLConnect与SQLInsert脚本编写以及Excel VBA报表模板制作和数据库恢复等模块。文档从数据库登录方式调整讲到Intouch变量与表单字段的对应绑定再到每5秒轮询写入与分钟条件判断逻辑并给出报表模板的控制面板与参数日报页面设计思路便于读者按章节对照实操。目前已有531人学习下载适合需要打通Intouch与SQL数据链路、构建可定期刷新Excel报表的中级自动化从业者参考。1. 从 Intouch 到 SQL Server 再到 Excel一份能直接落地的 SCADA 报表链路拆解很多做 SCADA 现场实施的朋友都遇到过这个场景Intouch 画面上实时数据跑得好好的但一到“出报表”就抓瞎——历史数据散在内存标记里班组长要的日报、周报、月报全靠人工抄表Excel 里手动填数错一行就得从头核。这份文档解决的正是这个痛点在 WonderWare Intouch 2014 R2IDE 平台里把实时数据定时写入 SQL Server 2012再用 Excel 的 VBA 报表系统把数据拉出来形成可视化报表最后还附带了数据库文件的备份与恢复操作。整条链路覆盖了数据库配置、Intouch 标记与脚本、SQL 绑定列表、Excel 模板 VBA 引用、以及 mdf/ldf 文件的附加恢复。适合正在做 Intouch 报表模块的自动化工程师、SCADA 系统集成人员以及需要把现场数据沉淀到关系型数据库做二次分析的从业者。下面按“数据怎么进去 → 报表怎么出来 → 库怎么恢复”的顺序把每一步的参数和坑讲透。2. 打通 Intouch 到 SQL Server 2012登录方式、标记字典与绑定列表2.1 为什么先改 SQL Server 身份验证模式Intouch 通过 SQLConnect 函数连接数据库时用的是连接字符串里的 UserID 和 Password这属于 SQL Server 身份验证。而 SQL Server 2012 默认安装后往往只启用了 Windows 身份验证模式这时候你用 sa 账号怎么连都连不上报错通常是“用户 ‘sa’ 登录失败”或者“无法打开登录所请求的数据库”。所以第一步必须进 SQL Server Management Studio在服务器属性 → 安全性里把身份验证方式改成“SQL Server 和 Windows 身份验证模式”确定后重启 SQL Server 服务生效。改完模式之后还要确认 sa 账号是启用状态。在对象资源管理器的“安全性 → 登录名”下找到 sa右键属性常规页里改密码状态页里确认“登录”是已启用。如果你不想用 sa也可以新建一个专用登录名但记得在“服务器角色”里至少给 public 和 db_datareader、db_datawriter否则 Intouch 写入时会因为权限不足被拒。这一步的常见做法是给报表单独建一个账号只授权目标数据库的读写避免用 sa 到处连。数据库和表单的建立相对直接新建数据库比如 BZ_BB 或 YADC_BB然后在里面建一张表列名要和后面 Intouch 绑定列表里的变量名一一对应。比如你打算记录日期、时间、温度、压力那表里就建对应的列数据类型也要匹配——Intouch 的内存整型对应 SQL 的 int内存实型对应 float内存消息对应 nvarchar。这里有个容易翻车的地方Intouch 的标记名如果带了特殊字符或者中文绑定列表里可能识别不了建议全用英文加下划线。2.2 标记名字典里必须建的三个内存标记在 Intouch 的标记名字典里有三个标记是整条链路的“基础设施”缺一个脚本就跑不起来。第一个是 Connid1类型选“内存整型”。它的作用是保存 SQLConnect 返回的连接编号后续 SQLInsert、SQLDisconnect 都要靠这个编号找到对应的连接。你可以把它理解成数据库连接的“句柄”没有它后面所有 SQL 函数都不知道该往哪个连接上发指令。第二个是 GetDateTimeS类型选“内存消息”。这个标记用来存连接时的时间字符串方便在报表里记录数据写入的时间戳。内存消息类型在 Intouch 里就是字符串长度默认 131 个字符够放标准日期时间格式了。第三个是 NodeName类型也是“内存消息”。它存的是本机计算机名脚本里用它来判断当前节点是不是需要执行写入的那台机器。多节点部署时这个判断很关键不然每台机器都往同一个库里写数据就重复了。除了这三个你还需要一个 WSQLS类型选“内存离散”。它是写入标志位用来防止同一分钟内重复写入。逻辑是写入前检查 WSQLS 是否为 0写完置 1下一分钟再复位为 0。没有这个标志位条件脚本每触发一次就写一条一分钟能给你灌进去几十条重复数据。2.3 条件脚本的触发时机与 SQLConnect 参数拆解Intouch 的脚本分两类条件脚本和应用程序脚本。数据写入的逻辑一般放在条件脚本里用系统时间做触发条件。文档里用的是$Second3和$Second5意思是每秒判断一次当秒数等于 3 或 5 时执行对应脚本。这里要注意$Second是系统秒时间范围 0 到 59条件类型选“为真时”表示条件成立的那一刻执行一次。数据库连接脚本通常放在应用程序脚本的“启动时”或者条件脚本里。核心函数是SQLConnect(Connid1, providersqloledb;Data SourceZHWSC02;Initial CatalogBZ_BB;UserIDsa;Passwordsingle);这行代码里Connid1 是前面建的内存整型标记用来接收连接编号。连接字符串分五段每段用分号隔开等号前面是参数名后面是值。Provider 固定写 sqloledb这是 SQL Server 的 OLE DB 提供程序Data Source 填数据库所在计算机名如果你数据库和 Intouch 在同一台机器上可以填 localhost 或者计算机名Initial Catalog 是数据库名必须和你实际建的库名一致User ID 和 Password 就是登录名和密码。参数改错是最常见的翻车点。Data Source 填了 IP 但 SQL Server 没开 TCP/IP 协议连不上Initial Catalog 拼错一个字母报“无法打开登录所请求的数据库”Password 里有分号连接字符串会被截断。我一般建议在 SSMS 里先用同样的账号密码手动登录一次确认能进再往脚本里填。2.4 绑定列表变量名与列名的强制对应关系绑定列表在 Intouch 左侧工具视图的 SQL 访问管理器里叫“绑定列表(B)”。它的作用是把 Intouch 的标记和 SQL 表的列关联起来。新建一个绑定列表名字比如叫 TDays然后在里面添加行每行选一个 Intouch 标记再选对应的数据库列名。这里有一条硬规则绑定列表里的变量名必须和 SQL 表里的列名完全一致数据类型也必须匹配。比如 Intouch 里有个内存实型标记叫 Temp1SQL 表里就得有个 float 类型的列叫 Temp1。名字对不上SQLInsert 执行时会报“无效的列名”类型对不上比如 Intouch 是字符串但 SQL 列是 int写入时直接失败而且错误信息不一定明确指向类型问题可能只报一个泛泛的“插入失败”。绑定列表建好之后SQLInsert 函数才能用SQLInsert(Connid1, TDays, TDays);第一个参数是连接编号第二个参数是 SQL 表名第三个参数是绑定列表名。注意这两个 TDays 含义不同第一个是数据库里的表单名称第二个是 Intouch 里建的绑定列表名称。文档里特意强调了这一点因为很多人会以为两个都填表名结果绑定列表名对不上写入的数据全是空值。2.5 完整写入脚本的逻辑链与关闭时清理把上面的碎片拼起来一个完整的写入脚本大概长这样GetNodeName(NodeName, 131); IF NodeName OP_OP1 THEN IF $Minute 58 THEN IF WSQLS 0 THEN SQLInsert(Connid1, TDays, TDays); WSQLS 1; ENDIF; ELSE WSQLS 0; ENDIF; ENDIF;逐行解释第一行 GetNodeName 把本机计算机名取到 NodeName 变量里131 是字符串最大长度。第二行判断当前机器是不是 OP_OP1只有这台机器执行写入避免多节点重复写。第三行判断当前分钟数是否大于等于 58意思是每小时的最后两分钟才触发写入这样一天下来就是 24 条记录适合做小时级报表。第四行检查 WSQLS 是否为 0确保这一分钟内还没写过。第五行执行插入。第六行把 WSQLS 置 1标记已写。如果分钟数不到 58走 ELSE 分支把 WSQLS 复位为 0为下一个小时做准备。退出应用程序时必须在“应用程序脚本”的“关闭时”里加一行SQLDisconnect(Connid1);不写这行数据库连接不会主动释放时间长了 SQL Server 那边会积累一堆睡眠连接严重时把连接池占满新的连接请求全部被拒。这个坑我在现场见过不止一次表现是系统跑几天之后突然写不进数据重启 Intouch 又好了根源就是连接没关。3. Excel 报表系统模板结构、VBA 引用与数据拉取3.1 报表模板的两个核心 Sheet 与 VBA 入口Excel 报表模板不是随便建个表格就行它需要两个关键 Sheet一个是“控制面板”用来放按钮、时间选择、查询条件另一个是“参数日报”用来展示从 SQL Server 拉出来的数据。控制面板上一般会放一个“刷新”按钮绑定 VBA 宏点击后触发数据查询和写入。进入 VBA 编辑器的方式是在“参数日报”Sheet 标签上右键 → 查看代码或者按 AltF11。进去之后在“工具”菜单里选“引用”这一步非常关键漏了后面代码全报“用户定义类型未定义”。3.2 VBA 引用里必须勾选的三项文档里说“至少钩选以下三项”结合 SQL Server 2012 和 Excel 的常规搭配这三项通常是引用名称作用不勾选的后果Microsoft ActiveX Data Objects 2.8 Library提供 ADO 连接和记录集对象无法使用 Connection、RecordsetMicrosoft ActiveX Data Objects Recordset 2.8 Library记录集相关补充部分 Recordset 方法不可用Microsoft Excel 16.0 Object LibraryExcel 对象模型无法操作 Sheet、Range版本号可能因 Office 版本不同而有差异比如 15.0 对应 Office 201316.0 对应 2016 及以上。选的时候挑版本号最高的那个通常没问题。如果列表里找不到 ADO 引用说明系统里没装 MDAC 或者 ADO 组件需要单独安装。勾完引用之后VBA 里就可以用 ADO 连接 SQL Server 了。一个典型的查询过程是创建 Connection 对象用连接字符串打开数据库执行 SELECT 语句把数据取到 Recordset再把 Recordset 逐行写到“参数日报”Sheet 的指定区域。连接字符串的格式和 Intouch 里类似但 VBA 用的是 ADO 的 ProviderDim conn As New ADODB.Connection Dim rs As New ADODB.Recordset conn.ConnectionString ProviderSQLOLEDB;Data SourceZHWSC02;Initial CatalogBZ_BB;User IDsa;Passwordsingle; conn.Open rs.Open SELECT * FROM TDays WHERE 日期 Range(B2).Value , conn这里 Range(B2) 是控制面板上用户选的起始日期。SQL 语句里拼接字符串要注意日期格式SQL Server 默认认 yyyy-MM-dd如果 Excel 单元格是 yyyy/M/d直接拼进去可能查不到数据。稳妥的做法是在 VBA 里用 Format 函数统一转成 yyyy-MM-dd 再拼。3.3 报表数据刷新的触发方式与性能边界报表刷新有两种触发方式手动按钮和定时刷新。手动按钮就是在控制面板上放一个 Shape右键指定宏用户点一下查一次。定时刷新用 Application.OnTime 方法比如每 5 分钟自动查一次。但定时刷新要小心如果查询数据量大、SQL 响应慢上一次还没查完下一次又触发了Excel 会卡死。我一般建议手动刷新为主定时刷新间隔不低于 10 分钟并且加一个状态标志位防止重入。数据量方面Excel 单 Sheet 能撑住的行数上限是 104 万行左右但实际报表没人会拉这么多。日报表一天 24 条月报表 720 条年报表 8760 条对 Excel 来说毫无压力。真正影响性能的是查询本身——如果 SQL 表没建索引日期范围查询会全表扫描几万条数据就能让 Excel 转圈半分钟。所以建表的时候在日期列上建个非聚集索引查询速度会快一个数量级。3.4 报表模板的复用与参数化设计一套好的报表模板不应该每次新建都从头画。我的做法是把控制面板上的查询条件做成参数区起始日期、结束日期、报表类型日报/月报三个单元格VBA 根据这三个参数动态拼 SQL。报表类型决定 GROUP BY 的粒度——日报按天分组月报按月分组。这样一套模板能覆盖多种报表需求不用维护多个文件。参数区的单元格最好加数据验证日期列限制只能输入日期格式避免用户填了文本导致 SQL 拼接出错。另外控制面板上可以放一个“导出”按钮把查询结果另存为新的 xlsx 文件方便发给不装数据库客户端的同事。4. SQL Server 数据库恢复mdf/ldf 文件附加与常见报错4.1 数据库文件构成与备份目录约定SQL Server 的数据库物理文件有两个一个是数据文件 .mdf一个是日志文件 .ldf。文档里数据文件叫 YADC_BB.mdf日志文件叫 YADC_BB_log.ldf存放在 E:\DataBase 下。备份的时候这两个文件必须一起拷只拷 mdf 不拷 ldf附加时会报“日志文件丢失”或者“无法重建日志”。如果 ldf 真的丢了也不是完全没救可以用ATTACH_FORCE_REBUILD_LOG方式强制重建但可能丢事务生产环境慎用。恢复的流程是把 DataBase 目录整个拷到目标服务器的 E:\ 下然后打开 SSMS在“数据库”节点上右键 → 附加在弹出的窗口里点“添加”定位到 E:\DataBase\YADC_BB.mdf确定后 SSMS 会自动识别同目录下的 ldf 文件。如果 ldf 不在同目录或者名字对不上附加会失败这时候需要手动指定日志文件路径。4.2 附加失败的三种典型场景与排查第一种权限不足。报错“操作系统错误 5拒绝访问”。原因是 SQL Server 服务账号没有 E:\DataBase 目录的读取权限。解决办法是给 SQL Server 服务账号通常是 MSSQL$实例名或者 Network Service授予该目录的读权限或者把文件放到 SQL Server 默认的数据目录下再附加。第二种文件被占用。报错“无法打开物理文件操作系统错误 32另一个程序正在使用此文件”。这通常是因为源服务器上数据库还在运行mdf 文件被锁着。拷贝之前先在源服务器上把数据库脱机或者停掉 SQL Server 服务再拷文件。第三种版本不兼容。报错“数据库版本 706 无法打开此服务器支持版本 655 及更低版本”。这是高版本 SQL Server 的备份文件拿到低版本上附加比如 SQL Server 2012 的库拿到 2008 R2 上附加。这种情况没有直接解决办法只能在同版本或更高版本上附加然后用导出导入的方式迁移数据。4.3 附加完成后的验证与连接测试附加成功后数据库会出现在 SSMS 的对象资源管理器里。别急着收工先做两件事一是执行一条SELECT COUNT(*) FROM TDays确认表和数据都在二是在 Intouch 里把连接字符串的 Initial Catalog 改成新附加的库名跑一次写入脚本看数据能不能正常插进去。这两步都过了才算恢复完成。如果附加后表不见了大概率是附加到了错误的 mdf 文件或者原库里有多个数据文件但只拷了一个。SQL Server 的数据库可以包含多个 .ndf 次要数据文件备份时必须全部拷贝漏一个附加出来的库就是残缺的。5. 几个让我熬夜排查的坑从连接字符串到 VBA 引用5.1 连接字符串里的分号与密码特殊字符现象SQLConnect 返回错误提示“初始化字符串的格式不符合规范”。原因密码里带了分号连接字符串按分号分段密码被截断后面的参数全部错位。解决密码里避免用分号或者用花括号把密码包起来比如Password{sin;gle}。但 Intouch 的 SQLConnect 对花括号支持不一定好最稳妥的办法是改密码别用特殊字符。5.2 绑定列表变量名大小写不一致现象SQLInsert 执行后数据库里多了一行但所有列都是 NULL。原因Intouch 标记名是 Temp1SQL 列名是 temp1SQL Server 默认不区分大小写但 Intouch 的绑定列表匹配是区分大小写的它找不到对应列就写空值。解决建表时列名和 Intouch 标记名保持完全一致的大小写别偷懒。5.3 Excel 加载项被禁用导致 VBA 宏跑不起来现象打开报表模板点刷新按钮没反应或者提示“宏已被禁用”。原因Excel 的安全设置把宏禁用了或者文件来自网络被标记为“受信任位置”之外。解决文件 → 选项 → 信任中心 → 信任中心设置 → 宏设置选“启用所有宏”同时把报表模板所在目录加到“受信任位置”。如果文件是从邮件或聊天工具下载的右键属性里可能有个“解除锁定”的勾选框勾上再打开。5.4 SQL Server 2012 密码到期导致连接突然中断现象系统跑了几个月一直正常某天突然写不进数据SQLConnect 报“登录失败”。原因sa 账号的密码设置了过期策略到期后账号被锁定。解决在 SSMS 里把 sa 的“强制实施密码过期策略”取消勾选或者定期改密码。生产环境建议用不过期的专用账号别用 sa。5.5 条件脚本触发频率过高导致数据重复现象数据库里同一分钟有几十条记录报表数据翻倍。原因条件脚本写成了$Second3而不是$Second3秒数从 3 到 59 每秒都触发一次。解决条件必须用精确匹配配合 WSQLS 标志位做二次防护。改完脚本后把数据库里重复的数据清掉不然报表统计全是错的。6. 进阶把报表查询做成参数化存储过程顺带解决 Excel 日期格式玄学前面 VBA 里直接拼 SQL 字符串简单场景够用但遇到日期格式和 SQL 注入就头疼。我的习惯是把查询逻辑下沉到 SQL Server 的存储过程里VBA 只负责传参数和接收结果。这样做的好处是日期格式在存储过程里统一处理VBA 不用管查询逻辑改的时候只改数据库不用重新分发 Excel 模板。先建一个存储过程CREATE PROCEDURE GetReportData StartDate DATE, EndDate DATE, ReportType NVARCHAR(10) AS BEGIN IF ReportType Daily SELECT CONVERT(DATE, 日期) AS 日期, AVG(Temp1) AS 平均温度, MAX(Temp1) AS 最高温度 FROM TDays WHERE 日期 StartDate AND 日期 EndDate GROUP BY CONVERT(DATE, 日期) ORDER BY 日期; ELSE IF ReportType Monthly SELECT YEAR(日期) AS 年, MONTH(日期) AS 月, AVG(Temp1) AS 平均温度 FROM TDays WHERE 日期 StartDate AND 日期 EndDate GROUP BY YEAR(日期), MONTH(日期) ORDER BY 年, 月; END;这个存储过程接收三个参数起始日期、结束日期、报表类型。日报按天分组算平均和最高温度月报按月分组算平均温度。VBA 里调用的时候用 ADO 的 Command 对象Dim cmd As New ADODB.Command cmd.ActiveConnection conn cmd.CommandType adCmdStoredProc cmd.CommandText GetReportData cmd.Parameters.Append cmd.CreateParameter(StartDate, adDate, adParamInput, , Range(B2).Value) cmd.Parameters.Append cmd.CreateParameter(EndDate, adDate, adParamInput, , Range(B3).Value) cmd.Parameters.Append cmd.CreateParameter(ReportType, adVarWChar, adParamInput, 10, Range(B4).Value) Set rs cmd.Execute用参数化调用之后日期格式的问题就消失了——ADO 会把 Excel 的日期值转成 SQL Server 认识的 DATE 类型不用再手动 Format。而且存储过程里可以加索引提示、可以分页、可以做更复杂的聚合比在 VBA 里拼字符串灵活得多。还有一个实际场景报表需要显示“本班次”的数据班次划分是 8 点到 16 点、16 点到 24 点、0 点到 8 点。这种逻辑放在存储过程里就是一个 CASE WHEN放在 VBA 里就得写一堆 If-Else。所以只要报表逻辑稍微复杂一点我都建议往存储过程迁移。最后说一个 Excel 日期格式的玄学问题。有时候存储过程返回的日期在 Excel 里显示成数字比如 44562这是因为单元格格式没设成日期。解决办法是在 VBA 写入数据后把日期列的 NumberFormat 设成 yyyy-mm-dd。还有一种情况是 Excel 把日期识别成了文本左对齐而不是右对齐排序和筛选都会乱。这时候用CDate函数强制转换一下再写入。从那以后我每次部署报表系统都强制走一遍“存储过程 参数化调用 日期格式校验”的流程再也没被日期格式坑过。希望帮到你。本文还有配套的精品资源点击获取