September 11, 2014 at 1:50 pm
We have a stored procedure that makes a decision to pull records from one of two servers.
If one of these servers became unavailable but was no longer queried would the stored procedure still work? Doesn't SQL recompile stored procedures periodically?
something like this:
If @ServerAIsDown=1
begin
select * from ServerB.dbo.MyTable
end
else
begin
select * from ServerA.dbo.MyTable
end
September 12, 2014 at 2:22 am
The stored procedure would error when trying to query the linked server that was down. It should recompile ok as the linked server itself is there
You could possibly do a check in the stored procedure to see if linked server was available first before querying. Here's an example
http://blog.h2consultants.com/2012/test-to-see-if-linked-server-is-available
I've not used this stored proc myself before so might want to test, especially to see how quickly it returns when testing a linked server that is not there.
September 16, 2014 at 4:49 pm
I'm virtually certain you'll get an error if the linked server isn't there.
You could use dynamic SQL, which will only be compiled if/when it's run.
As to recompiles, SQL will not arbitrarily recompile any procedure at a given point in time, only if/when the proc is run.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply