StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POprevent error when target database in restore mode (sql preparation)
text
Body
copied!<p>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.</p> <pre><code>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 </code></pre> <p>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 <strong>before</strong> 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.</p> <p>As a workaround I placed the INSERT statement in an execute function:</p> <pre><code>EXECUTE('INSERT INTO _LOCALTABLE (A, B) SELECT A, B FROM _LINKEDSERVER._DATABASENAME.dbo._REMOTETABLE') </code></pre> <p>But is there another more elegant solution to prevent the evaluation of this statement before this part of the sql is used?</p> <p>My scenario involves a linked server. Off course the same issue is when the database is on the same server.</p> <p>I was hoping for some command I am not aware of yet, that prevents evaluation syntax inside an IF:</p> <pre><code>IF(Evaluation) BEGIN PREPARE THIS PART ONLY IF Evaluation IS TRUE. END </code></pre> <p>edit regarding answer:</p> <p>I tested:</p> <pre><code>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 </code></pre> <p>Which still generates this error: Msg 942, Level 14, State 4, Line 8 Database 'Database' cannot be opened because it is offline.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload