当目标数据库处于还原模式时（SQL准备），防止出现错误

小编典典

当目标数据库处于还原模式时（SQL准备），防止出现错误

sql

我有一个每晚运行的存储过程。它从链接的服务器中提取一些数据，并将其插入运行sql
agent作业的服务器上的表中。在运行INSERT语句之前，该过程将检查链接服务器上的数据库是否在线（STATE =
0）。如果不是，则不运行INSERT语句。

IF EXISTS(
SELECT *
FROM OPENQUERY(_LINKEDSERVER,'
SELECT name, state FROM sys.databases
WHERE name = ''_DATABASENAME'' AND state = 0')
)
BEGIN
INSERT INTO _LOCALTABLE (A, B)
SELECT A, B FROM _LINKEDSERVER._DATABASENAME.dbo._REMOTETABLE
END

但是，当远程数据库处于还原模式时，该过程将给出错误消息（延迟的准备工作无法完成）。这是因为BEGIN和END之间的语句是在运行整个脚本
之前求值的。同样，如果IF评估不正确。并且因为_DATABASENAME处于还原模式，这已经给出了错误。

解决方法是将INSERT语句放在execute函数中：

EXECUTE('INSERT INTO _LOCALTABLE (A, B) 
SELECT A, B FROM _LINKEDSERVER._DATABASENAME.dbo._REMOTETABLE')

但是，在使用这部分sql之前，还有另一种更优雅的解决方案可以阻止对此语句的求值吗？

我的方案涉及一个链接服务器。当然，当数据库位于同一服务器上时，同样的问题也是。

我希望获得一些我尚不知道的命令，以防止IF内的评估语法：

IF(Evaluation)
BEGIN
    PREPARE THIS PART ONLY IF Evaluation IS TRUE.
END

编辑有关答案：

我测试了：

IF(EXISTS
(
SELECT *
FROM sys.master_files F WHERE F.name = 'Database'
AND state = 0
))
BEGIN
    SELECT * FROM Database.dbo.Table
END
ELSE
BEGIN
    SELECT 'ErrorMessage'
END

仍然会产生此错误：消息942，级别14，状态4，行8数据库’数据库’无法打开，因为它处于脱机状态。

阅读 127

2021-04-22

共1个答案

小编典典

我认为没有一种方法可以有条件地仅准备t-sql语句的一部分（至少不是您所询问的方式）。

原始查询的根本问题不是远程数据库有时处于脱机状态，而是远程数据库处于脱机状态时查询优化器无法创建执行计划。从这种意义上讲，离线数据库实际上就像语法错误一样，即，它是阻止创建查询计划的条件，因此整个事情在有机会执行之前就失败了。

之所以EXECUTE对您有用，是因为它推迟了传递给它的查询的编译，直到调用它的查询的运行时为止，这意味着您现在可能有两个查询计划，一个用于您的主查询，用于检查是否查看远程数据库。是可用的，并且只有在EXECUTE实际执行该语句之前，才会创建另一个。

因此，当您以这种方式考虑时，使用EXECUTE（或选择sp_executesql）并不是一种替代方法，它是一种可能的解决方案。这只是一种将查询分为两个单独的执行计划的机制。

考虑到这一点，您不一定必须使用动态SQL来解决您的问题。您可以使用第二个存储过程来达到相同的结果。例如：

-- create this sp (when the remote db is online, of course)
CREATE PROCEDURE usp_CopyRemoteData 
AS
BEGIN
  INSERT INTO _LOCALTABLE (A, B)
  SELECT A, B FROM _LINKEDSERVER._DATABASENAME.dbo._REMOTETABLE;
END
GO

然后，您的原始查询如下所示：

IF EXISTS(
  SELECT *
  FROM OPENQUERY(_LINKEDSERVER,'
  SELECT name, state FROM sys.databases
  WHERE name = ''_DATABASENAME'' AND state = 0')
  )
BEGIN
  exec usp_CopyRemoteData;
END

另一个解决方案是不用费心检查远程数据库是否可用，而只是尝试运行该INSERT INTO _LOCALTABLE语句，并在失败时忽略该错误。我在这里有点滑稽，但是除非您有一个ELSEfor IF EXISTS，即除非远程db脱机时您做一些不同的事情，否则您基本上只是抑制（或忽略）该错误。功能结果相同，因为没有数据被复制到本地表。

您可以在t-sql中使用try / catch进行操作，如下所示：

BEGIN TRY
  /* Same definition for this sp as above. */
  exec usp_CopyRemoteData;

  /* You need the sp; this won't work:
  INSERT INTO _LOCALTABLE (A, B)
  SELECT A, B FROM _LINKEDSERVER._DATABASENAME.dbo._REMOTETABLE
  */
END TRY
BEGIN CATCH
  /* Do nothing, i.e. suppress the error. 
    Or do something different?
  */
END CATCH

公平地说，这将抑制sp引发的所有错误，而不仅仅是远程数据库脱机引起的错误。而且您仍然具有与原始查询相同的根本问题，并且将需要存储的proc或动态SQL来正确捕获问题中的错误。BOL有一个很好的例子。有关详细信息，请参见此页的“不受TRY-
ATCH构造影响的错误”部分：http ://technet.microsoft.com/zh-
cn/library/ms175976(v=sql.105) .aspx

最重要的是，您需要将原始查询分为多个批次，并且有很多方法可以做到这一点。最佳解决方案取决于您的特定环境和要求，但是如果您的实际查询与本问题中提出的查询一样简单，那么您的原始解决方法可能就是一个好的解决方案。

2021-04-22