Update transactions have no results.

I use a three part solution.

First - create a table with one field that is an Identity field:

CREATE TABLE XXSequenceTable
(
    ID BIGINT IDENTITY
);

Next, create a SQL Procedure that will start a transaction, insert a record and rollback that transaction.  Table identity numbers are not reset within a Begin/Rollback pair.  The procedure will return the current Identity value for the table (which has been incremented by one).

CREATE PROCEDURE dbo.GetSEQUENCE ( @value BIGINT OUTPUT)
AS
    --Act like we are INSERTing a row to increment the IDENTITY
    BEGIN TRANSACTION;
    INSERT XXSequenceTable WITH (TABLOCKX) DEFAULT VALUES;
    ROLLBACK TRANSACTION;
    --Return the latest IDENTITY value.
    SELECT @value = SCOPE_IDENTITY();
GO

DECLARE @value BIGINT;
EXECUTE dbo.GetSEQUENCE @value OUTPUT;
SELECT @value AS [@value];
GO

Now write some ugly code using CS0120 and pull the value out.  It comes back as the dreaded Byte Array so you get to do some math to return the actual number:

    Dim p0 As Integer
    Dim p1 As Integer
    Dim p2 As Integer
    Dim p3 As Integer

    Dim q As AccpacCOMAPI.AccpacView
    DBLink.OpenView "CS0120", q
    q.Browse "DECLARE @value BIGINT; EXECUTE dbo.GetSEQUENCE @value OUTPUT; SELECT @value AS [@value];", True
    q.InternalSet 256
    q.Fetch
    p0 = CInt(q.Fields(0).Value(0))
    p1 = CInt(q.Fields(0).Value(1))
    p2 = CInt(q.Fields(0).Value(2))
    p3 = CInt(q.Fields(0).Value(3))
    
    LogUNIQ = p0 + (p1 * (2 ^ 8)) + (p2 * (2 ^ 16)) + (p3 * (2 ^ 24))
   

Thanks Django, I will try to implement it this way.

Thanks Jay; I know that update statements have no results but the transaction also contains a select statement which does not seem to execute . If I put the select statement first then it executes it and it gives me the result  but it does not execute the Update statement.

I would like both executed, ideally in this order : update first and then SELECT and then get the results of the select statement

If I use the transaction as it is in SQL server management studio I executes the update statement and then gives me the result of the select statement

The same SQL transaction used through the CS0120 executes only the first statement in the transaction.

Yes, CS0120 has lots of limits.  For your example I would probably have added a simple trigger to the Sage table.

After additional testing it turns out that both select statement and update statement execute but the Fetch reads the results of only the first statement. So if I put the select statement first and update second I get what need. Thanks for your input.