sp_testlinkedserver kills transaction

49 Views Asked by At

I am trying to validate if my linked server is up:

CREATE PROCEDURE [dbo].[GET_Validate_LinkedServer]
    @LinkedServer NVARCHAR(50),
    @Status BIT = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @RetVal int = 0,
            @SysServerName sysname;
            
    -- prevent kill transaction when raiserror is fired
    SET XACT_ABORT OFF;
    PRINT XACT_STATE()

    BEGIN TRY
        SET @SysServerName = CONVERT(SYSNAME, @LinkedServer);
        EXEC @RetVal = sys.sp_testlinkedserver @SysServerName;
        SET @Status = 1;
    END TRY
    BEGIN CATCH
        SET @Status = 0;
    END CATCH; 

    -- ROLLBACK OPTION TO ORIGINAL STATE
    SET XACT_ABORT ON;
END

But when validation was called

BEGIN TRANSACTION
    BEGIN TRY
        DECLARE @Status BIT

        EXEC master.dbo.GET_Validate_LinkedServer 'E21', @Status OUTPUT

        SELECT @Status
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE()
    END CATCH

It kills my transaction generating the following error

Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

How can I prevent this error from being generated? or how can I ignore it?

0

There are 0 best solutions below