在PL/SQL中，执行sqlcmd（访问文件数据库）

现状：一个每日自动进行的Batch处理发生了为执行异常，导致客户后序的自动Batch处理发生一系列错误。

 概要分析过程：上午9：40收到客户发送过来的异常Error，后续发生的一系列错误，此时Job中心还在按计划生成

各种张票报告，检查Job中心的执行单，平时，15秒钟可完成的处理，这次耗费了20分钟，最后异常终了。

相关Log文件，只有启动和异常终了的三两行平常Log文件。 而Batch处理，其核心作业全部在PL/SQL中执行

并完成。 

 

原因追查，耗费20分钟，初步认定DB SERVER为主要疑点，同时察看数据库主体的执行Log.

本文主要讨论后者。

 

因为是Batch处理，所有的处理都用PL/SQL进行，中间生成的操作及异常Log全部保存到ErrorLog的一个表中。

但是在异常情况下，PL/SQL中有Rollback的操作，一旦异常发生，所有的相关操作记录即时保存到ErrorLog表中，

但在最后回滚的时候，LOG全部清除掉了，这在SQL SERVER中，基本是个难题（继要利用回滚，同时又需要生成ErrorLog

表） 。ORACLE DATABASE中，在同一个Session中，可以允许执行和永久写入。 

 

在这种情况下，考虑将写入Table中的Log，同时写入Log文件中，这样即使发生异常，数据回滚，Log文件中仍能看到

相关的操作记录。

在SQL语句中，执行SQLCMD命令文，在SQL Server中是被支持和允许的。 

 

1.  赋予xp_cmdshell可执行权限。

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1;

GO -- To update the currently configured value for advanced options.

RECONFIGURE;

GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1;

GO -- To update the currently configured value for this feature. RECONFIGURE; GO

 

 2. 执行将DB数据存入Log文件中

DECLARE @cmd sysname, @var sysname;

SET @var = 'dir/p';

SET @cmd = @var + ' > dir_out.txt';

EXEC master..xp_cmdshell @cmd;

 

 3. 考虑到xp_cmdshell 命令是synchronously，即其处理时同期的。如下的操作将会发生致命错误

DECLARE @fileName as  NVARCHAR(100)

DECLARE @SqlCmd as NVARCHAR(4000)

declare @timestring  as varchar(20) 

SET @fileName = 'C:\LOG\Batch' + @timestring + '.LOG';

set @SqlCmd = 'ｈ-S . -d '+  db_name() +' -E -s, -W -Q "SELECT * FROM LogMessage7" > ' + @fileName;

SELECT @SqlCmd

EXEC xp_cmdshell @SqlCmd

DELETE FROM LogMessage7

当执行将数据写入到filename路径文件时，因为同步处理的原因，LogMessage7表会被Select及Delete同时操作。SQL SERVER

进入死循环。相关的写出文件fileName也会被琐死不能被释放。 

同理，如果在BEGIN TRANSACTION处理后，进行LogMessage7表的插入动作，而在执行的ROLLBACK  TRANSACTION之前

执行了 xp_cmdshell （引用LogMessage7表），将同样造成相互锁死。

参考： 

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql

https://docs.microsoft.com/ja-jp/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option

https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility 

http://msdn.microsoft.com/ja-jp/library/ms161956.aspx