StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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).</p> <p>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.</p> <p>The reason <code>EXECUTE</code> 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 <code>EXECUTE</code> statement is actually executed.</p> <p>So when you think about it that way, using <code>EXECUTE</code> (or alternatively, <code>sp_executesql</code>) 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.</p> <p>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:</p> <pre><code>-- 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 </code></pre> <p>Then your original query looks like this:</p> <pre><code>IF EXISTS( SELECT * FROM OPENQUERY(_LINKEDSERVER,' SELECT name, state FROM sys.databases WHERE name = ''_DATABASENAME'' AND state = 0') ) BEGIN exec usp_CopyRemoteData; END </code></pre> <p>Another solution would be to not even bother checking to see if the remote database is available, just try to run the <code>INSERT INTO _LOCALTABLE</code> statement and ignore the error if it fails. I'm being a bit facetious, here, but unless there's an <code>ELSE</code> for your <code>IF EXISTS</code>, 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.</p> <p>You could do that in t-sql with a try/catch, like so:</p> <pre><code>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 </code></pre> <p>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: <a href="http://technet.microsoft.com/en-us/library/ms175976(v=sql.105).aspx" rel="nofollow">http://technet.microsoft.com/en-us/library/ms175976(v=sql.105).aspx</a></p> <p>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.</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