SQL Server TRY ... CATCH Is ​​Not Ловля Ошибка

Обновить

December 2018

Просмотры

867 раз

4
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

Мой первый опыт с помощью Try ... Catch в SQL Server не впечатлил меня до сих пор.

Я остановил SQL Service на моем связанном сервере, чтобы попытаться проверить ситуацию, когда наш связанный сервер вниз, недоступен и т.д.

Вместо того, чтобы ловить какую-либо ошибку, этот код просто бросает «Войти таймаут» и «связанные с сетью или ошибка, специфичными произошла ...» ошибку и прекращает выполнение остальной части коды.

Является ли мой SQL TRY ... CATCH блок не настроен правильно?

3 ответы

0

You need to create your end testlinkedserver stored procedure. This will also capture login time out errors.

exec dbo.USP_testlinkedserver 'myServerNameHere'

The definition is mentioned below:

CREATE PROCEDURE USP_testlinkedserver 
    @ServerName sysname
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @statement NVARCHAR(MAX), @errorMessage NVARCHAR(MAX)

    SET @statement = N'SELECT * FROM OPENQUERY('+QUOTENAME(@ServerName,'[')+', ''SELECT 1'')'

BEGIN TRY
    -- run the query
    EXEC sp_executesql @stmt = @statement;
END TRY
BEGIN CATCH
    -- show custom message
    SET @errorMessage=QUOTENAME(@ServerName,'[') + ' linked server is not available. ' + ERROR_MESSAGE()
    Raiserror(@errorMessage,16,1)
END CATCH;

END
0

From MSDN

Errors Unaffected by a TRY…CATCH Construct

TRY…CATCH constructs do not trap the following conditions:

  1. Warnings or informational messages that have a severity of 10 or lower.
  2. Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
  3. Attentions, such as client-interrupt requests or broken client connections.
  4. When the session is ended by a system administrator by using the KILL statement.

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  1. Compile errors, such as syntax errors, that prevent a batch from running.
  2. Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
3

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