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

我有一个每晚运行的存储过程。它从链接服务器中提取一些数据，并将其插入到运行 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，级别 14，状态 4，第 8 行无法打开数据库"数据库"，因为它处于脱机状态。

我认为

没有办法有条件地只准备 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 语句并在失败时忽略错误。我在这里有点滑稽，但除非有针对您的IF EXISTS的ELSE，即除非您在远程数据库脱机时执行不同操作，否则您基本上只是抑制（或忽略）错误无论如何。功能结果是相同的，因为没有数据被复制到本地表。

你可以在 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

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

我认为state = 0应该有效，

但是，有一种可能性是存在的，

如果数据库脱机（在维护/还原损坏的数据库需要使数据库脱机）下，则不会显示在sys.database_files

因此，请使用此sys.master_files，这将向您显示脱机数据库的状态。

另请参阅包含有关命令的更多详细信息的链接，

还要检查实现的还原命令/模型及其详细信息，因为完整恢复会使数据库脱机。

我认为这应该可以，如果没有，请回复，

不幸的是，阻止 sql 准备语句的唯一方法是使用动态 SQL。我有同样的问题，我添加一列然后想要更新它。如果不使更新语句成为动态 sql，就无法获取要编译的脚本。

相关内容

最新更新

热门标签：