none
Uncommittable transaction when testing connectivity to (offline) linked server RRS feed

  • Question

  • Issue: when testing connectivity to an offline linked server wrapped inside a transaction, my transaction becomes uncommittable. However, I still need to execute the remaining code in the stored procedure. I am using sys.sp_testlinkedserver for testing connectivity. How to get around this issue?

    Background:

    I have two servers: ServerA and ServerB. Both are running SQL Server 2016. On ServerA I have a production database, and on ServerB I have a Historian database.

    ServerA has a Linked Server object HISTORIAN which points to ServerB (using SQL User authentication) and has the default server options, except Collation Compatible which is set to True. Provider: SQL Server Native Client 11.0.

    On ServerA a stored procedure sp_A1 is executed which starts a production order. This stored procedure calls another stored procedure sp_A2 that queries the Linked Server via the four-part name syntax, which queries certain Historian data and stores this on the production database with the started order.

    Goal: if the Historian is not accessible (wrong user, server offline, MSSQLSERVER service not running) I still want to start the production order (we just don't store the Historian values in the production database).

    Constraints: sp_A1 is always wrapped in a transaction, because of the way the user application connects to SQL Server on ServerA. I cannot change this, but I can change the stored procedures itself and SQL Server configuration. I cannot separate the calls to sp_A1 and sp_A2, meaning all the work that needs to be done needs to be initiated by a single stored procedure.

    Example:

    As a simple example I have created the excerpt below. Referring to the background discussed above, everything inside the BEGIN/COMMIT TRANSACTION would be sp_A1. And everything between the first and last SELECT statement would be sp_A2.

    BEGIN TRANSACTION

        SELECT'execute some code...'

      DECLARE@return_code INT

      DECLARE@sSQL NVARCHAR(MAX)

      --Use try and catch to test whether the Historian is accessible through linked server

      BEGINTRY

        PRINT('Testing connection with the linked server.')

        -- Tests the connection to a linked server. If the test is unsuccessful the procedure raises an exception with the reason of the failure.

        EXECsys.sp_testlinkedserverHISTORIAN

      ENDTRY

      BEGINCATCH

        PRINT('Error with accessing Historian, error_code = -5')

        SELECT@return_code =-5;

      ENDCATCH

      SELECT@return_code

      IF@return_code <>-5

      BEGIN

        SET@sSQL ='SELECT value1,value2 FROM HISTORIAN.Runtime.dbo.History WHERE order_id = ''12345678'''

        EXECSP_EXECUTESQL@sSQL

      END

      SELECTXACT_STATE()

      SELECT'execute some more code...'

    COMMIT TRANSACTION

    Note that dynamic SQL is used to prevent syntax checking for the Linked Server on creation of the stored procedure (in cases where the linked server object has not yet been created, or when ServerB is not yet installed).

    What I find is that when I execute this code without BEGIN/COMMIT TRANSACTION (and my Linked Server is unreachable), no error is thrown. But, when including the BEGIN/COMMIT TRANSACTION statements, the transaction becomes uncommittable. From what I understand is that this means SAVEPOINTS do not help here. The issue is that I have no way around the BEGIN/COMMIT TRANSACTION statements (see Constraints).

    Is there a way around this? Or an alternative method to access the database on ServerB from a stored procedure on ServerA?

    Thanks for your help and thoughts.

    Wednesday, June 26, 2019 12:12 PM

All replies

  • If you want to perform this synchronously in T-SQL only, I think you are out of luck.

    Solutions I can think of:

    * Write your own sp_testlinkedserver in the CLR that tries to connect to Historian, and which suppresses all errors and only returns a return code.

    * Use Service Broker. You post a message on a Service Broker queue on which sp_A2 serves as the activation procedure. sp_A2 talks to the linked server, and returns data or error code. sp_A1 waits for response from sp_A2.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, June 26, 2019 9:31 PM