当目标数据库处于还原模式时(SQL准备)，防止出现错误 [英] prevent error when target database in restore mode (sql preparation)

问题描述

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

I have a stored procedure that runs nightly. It pulls some data from a linked server and inserts it into a table on the server where the sql agent job runs. Before the INSERT statement is run, the procedure checks if the database on the linked server is online (STATE = 0). If not the INSERT statement is not run.

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处于还原模式，这已经给出了错误.

But the procedure gives an error (deferred prepare could not be completed) when the remote database is in restore mode. This is because the statement between BEGIN and END is evaluated before the whole script is run. Also when the IF evaluation is not true. And because _DATABASENAME is in restore mode this already gives an error.

作为一种解决方法，我将INSERT语句放在了执行函数中:

As a workaround I placed the INSERT statement in an execute function:

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

但是还有另一种更优雅的解决方案可以防止在使用这部分sql之前评估此语句吗?

But is there another more elegant solution to prevent the evaluation of this statement before this part of the sql is used?

我的情况涉及一个链接服务器.当然，当数据库位于同一服务器上时，也会出现同样的问题.

My scenario involves a linked server. Off course the same issue is when the database is on the same server.

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

I was hoping for some command I am not aware of yet, that prevents evaluation syntax inside an 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行 无法打开数据库数据库"，因为它已脱机.

Which still generates this error: Msg 942, Level 14, State 4, Line 8 Database 'Database' cannot be opened because it is offline.

推荐答案

我认为没有一种方法可以有条件地仅准备一部分t-sql语句(至少不是您所要求的方式).

I don't think there's a way to conditionally prepare only part of a t-sql statement (at least not in the way you've asked about).

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

The underlying problem with your original query isn't that the remote database is sometimes offline, it's that the query optimizer can't create an execution plan when the remote database is offline. In that sense, the offline database is effectively like a syntax error, i.e. it's a condition that prevents a query plan from being created, so the whole thing fails before it ever gets a chance to execute.

EXECUTE为您工作的原因是，它推迟了传递给它的查询的编译，直到调用它的查询运行时为止，这意味着您现在有两个查询计划，一个用于检查您的主查询来查看远程数据库是否可用，以及是否要创建另一个数据库，除非并且直到实际执行EXECUTE语句为止.

The reason EXECUTE works for you is because it defers compilation of the query passed to it until run-time of the query that calls it, which means you now have potentially two query plans, one for your main query that checks to see if the remote db is available, and another that doesn't get created unless and until the EXECUTE statement is actually executed.

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

So when you think about it that way, using EXECUTE (or alternatively, sp_executesql) is not so much a workaround as it is one possible solution. It's just a mechanism for splitting your query into two separate execution plans.

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

With that in mind, you don't necessarily have to use dynamic SQL to solve your problem. You could use a second stored procedure to achieve the same result. For example:

-- 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

然后您的原始查询如下:

Then your original query looks like this:

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，即除非远程db脱机时您执行其他操作，否则您基本上只是抑制(或忽略)该错误.功能结果相同，因为没有数据被复制到本地表.

Another solution would be to not even bother checking to see if the remote database is available, just try to run the INSERT INTO _LOCALTABLE statement and ignore the error if it fails. I'm being a bit facetious, here, but unless there's an ELSE for your IF EXISTS, i.e. unless you do something different when the remote db is offline, you're basically just suppressing (or ignoring) the error anyway. The functional result is the same in that no data gets copied to the local table.

您可以在t-sql中使用try/catch来做到这一点，就像这样:

You could do that in t-sql with a try/catch, like so:

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…CATCH构造影响的错误"部分以获取详细信息:

To be fair, this would suppress all errors raised by the sp, not just ones caused by the remote database being offline. And you still have the same root issue as your original query, and would need a stored proc or dynamic SQL to properly trap the error in question. BOL has a pretty good example of this; see the "Errors Unaffected by a TRY…CATCH Construct" section of this page for details: http://technet.microsoft.com/en-us/library/ms175976(v=sql.105).aspx

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

The bottom line is that you need to split your original query into separate batches, and there are lots of ways to do that. The best solution depends on your specific environment and requirements, but if your actual query is as straightforward as the one presented in this question then your original workaround is probably a good solution.

这篇关于当目标数据库处于还原模式时(SQL准备)，防止出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！