Prevent an error when the target database is in recovery mode (sql preparation)

Question

I have a stored procedure that runs every night. It extracts some data from the linked server and inserts it into a table on the server on which the sql agent job is running. Before executing the INSERT statement, the procedure checks if the database is on the linked server on the network (STATE = 0). If the INSERT statement is not executed.

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 

But the procedure gives an error (delayed preparation cannot be completed) when the remote database is in recovery mode. This is because the instruction between BEGIN and END is evaluated before the entire script is executed. Also, when the IF score is incorrect. And since _DATABASENAME is in recovery mode, this already gives an error.

As a workaround, I put the INSERT statement in the execution function:

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

But is there an even more elegant solution to prevent evaluating this statement before this part of sql is used?

There is a linked server in my script. Of course, the problem is that the database is on the same server.

I was hoping for some kind of command that I still don't know about, which prevents evaluation syntax inside IF:

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

change the answer:

I tested:

 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 

Which still generates this error: Msg 942, level 14, state 4, line 8 Database "Database" cannot be opened because it is disabled.

Answer 1

I do not think that there is a way to conditionally prepare only part of the t-sql statement (at least not in the way you requested).

The main problem with the original query is not that the remote database is sometimes disconnected, that the query optimizer cannot create an execution plan when the remote database is offline. In this sense, an autonomous database is essentially a syntax error, that is, a condition that prevents the creation of a query plan, so all this error fails before it ever gets the opportunity to execute.

The reason EXECUTE works for you is because it delays the compilation of the request sent to it until the request completes, which calls it, which means that you have potentially two query plans, one for your main request, which checks to see if the remote is available db, and another that is not created until and until the EXECUTE statement is executed.

Therefore, when you think about it, using EXECUTE (or, alternatively, sp_executesql ) is not so much a workaround as one possible solution. This is just a mechanism for splitting your request into two separate execution plans.

Given this, you do not have to use dynamic SQL to solve your problem. You can use a second stored procedure to achieve the same result. For instance:

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

Another solution would not even be to check if the remote database is accessible, just try to run the INSERT INTO _LOCALTABLE and ignore the error if it fails. I'm a little addicted here, but if there is no ELSE for your IF EXISTS , i.e. Unless you do something else when the remote db is offline, you simply suppress (or ignore) the error anyway. The functional result is the same as the data is not copied to the local table.

You can do this in t-sql with try / catch, for example:

 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, ie suppress the error. Or do something different? */ END CATCH 

To be fair, this would suppress all errors caused by sp, and not just those that were caused by the remote database offline. And you still have the same root problem as the original query, and to store the false error, you need to store the stored proc or dynamic SQL. BOL has a pretty good example of this; see the section “Errors not affected by the TRY ... CATCH Construct section” of this page: http://technet.microsoft.com/en-us/library/ms175976(v=sql.105).aspx

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

Answer 2

I think state = 0 should work,

But there is one possibility

if the database is offline (when servicing / restoring a damaged database, you must disconnect the database offline), it will not be displayed in sys.database_files

so for this, use sys.master_files , this will show you the status for the database, which is offline.

Also link to a link that contains more detailed information about the team,

Also check the implemented recovery command / model and details , since full recovery disables the database offline.

I think this should happen if you don’t answer,

Answer 3

Unfortunately, the only way to get sql to prepare this statement for dynamic SQL is. I have the same problem when I add a column and then want to update it. It is not possible to compile a script without making the update statement dynamic sql.