sql - 防止目标数据库处于还原模式时出错(sql 准备)

Question

我有一个每晚运行的存储过程。
它从链接服务器中提取一些数据并将其插入到运行 sql 代理作业的服务器上的表中。在运行 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('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

这仍然会产生这个错误:
Msg 942, Level 14, State 4, Line 8
无法打开数据库“数据库”，因为它处于脱机状态。

Answer 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语句并在失败时忽略错误。我在这里有点开玩笑，但除非有 ELSE为您 IF EXISTS ，即除非您在远程数据库离线时执行不同的操作，否则您基本上只是在抑制(或忽略)错误。功能结果相同，没有数据被复制到本地表。

您可以在 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 引发的所有错误，而不仅仅是由远程数据库脱机引起的错误。而且您仍然有与原始查询相同的根本问题，并且需要存储过程或动态 SQL 来正确捕获有问题的错误。 BOL 有一个很好的例子；有关详细信息，请参阅本页的“不受 TRY…CATCH 构造影响的错误”部分: http://technet.microsoft.com/en-us/library/ms175976(v=sql.105).aspx

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