Menu
BEGIN TRY
EXEC N'EXEC sp_testlinkedserver N''[MyLinkedServer]'';';
END TRY
BEGIN CATCH
SELECT 'LinkedServerDown' AS Result
RETURN
END CATCH
SELECT TOP(1) FirstName FROM [MyLinkedServer].TestDatabase.dbo.Customer
My first experience with using a TRY...CATCH in SQL Server does not have me impressed so far.
I've stopped the SQL Service on my linked server to attempt to test a situation where our linked server is down, inaccessible, etc.
Instead of catching any error, this code just throws the "Login timeout expired" and "network-related or instance-specific error has occurred..." error and ceases execution of the rest of the code.
Is my SQL TRY...CATCH block not set up correctly?
611
So, try... catch can only handle errors that occur in valid code. Such errors are called “runtime errors” or, sometimes, “exceptions”. That's because the function itself is executed later, when the engine has already left the try...
The CATCH block functions Inside the CATCH block, you can use the following functions to get the detailed information on the error that occurred: ERROR_LINE() returns the line number on which the exception occurred. ERROR_MESSAGE() returns the complete text of the generated error message.
CATCH Construct section) some errors are not caught by CATCH statement. Particularly: Compile errors, such as syntax errors, that prevent a batch from running. Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
Option A is the correct choice. It is possible for all statements in a transaction to work and then the actual COMMIT to fail, so you keep the COMMIT inside your TRY block so that any failure of the COMMIT will be caught and you can gracefully handle this error and rollback.
As per the MSDN, what sp_testlinkedserver do is
Tests the connection to a linked server. If the test is unsuccessful the procedure raises an exception with the reason of the failure.
So when you compile your code (SP), sp_testlinkedserver checks for connection. But you can defer this and capture it by using dynamic SQL.
Like this -
BEGIN TRY
EXEC sp_executesql N'EXEC sp_testlinkedserver [192.168.51.81];';
END TRY
BEGIN CATCH
SELECT 'LinkedServerDown' AS Result
END CATCH
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
