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?