Distributed Transaction?

mrQQ

Hello,

for one of my tasks, I would really like to use a distributed transaction. I connect to remote sql server using MDAC, and need remote updates to be in same transaction as local. I think it should be possible to use Microsoft Distributed Transaction Controller and it's COM objects, but I'm not really sure as to where to start.

Has anyone tried something like this in their Dynamics projects?

Thanks!

Slawek_Guzek

Hi,

Start from reading about Distributed Transaction Coordinator service.

Then make connection to the remote database, using ADO automation objects (search Mibuso how to do that), and try to do some inserts or updates.

Once you've got error messages you will have a plenty of topics to search and learn, and more specific questions to ask

Slawek.

mrQQ

I have very specific question.

In single NAV function I make a connection to remote server using ADO and do some updates. Afterwards, I do some updates on local tables. If these fail, remote updates don't get rolled back.

I've read a bit on DTC, however, all the examples involve use of "using" construct, which is not available in NAV. So I'm not really sure as where to start.

Slawek_Guzek

They will never roll back automatically is such a case, I'm afraid. Why should they ?

If you're making ADO connection, ADO components doesn't know anything about any transactions on your local NAV SQL server. You are just sending SQL statement to remote server 'manually' from your code in client, not from SQL server.

Try to split your remote SQL execution into three pieces:
1. BEGIN TRAN
2. Actual SQL query updating table,
3. COMMIT TRAN

All three should be send using the same connection.

COMMIT TRAN should be executed after your local transaction is finished. If your code fails, and you don't send COMMIT TRAN, and your ADO connection will be closed then transacion on remote server will be rolled back.

DTC service will not be of any use here as what you are doing is NOT distributed transaction

It would be distributed transaction if you executed code on your local SQL server, and do some updates to remove tables via local SQL, either using OPENROWSET query, via linked server.

Slawek

mrQQ

I'm not sure that is correct.

How does SSIS manage to rollback such transactions if one of later local operations fail? It manages to do that even from non-MS servers! So it *is* possible

Slawek_Guzek

Read my answer again please.

I'm not saying that it is not possible. What I am saying is that you are NOT doing distributed transactions.

Slawek

mrQQ

How is what I'm doing different from what is SSIS doing?

Anyway, your suggestion about sending commit tran after local updates sounds very good. However, at the moment I'm using ADOCommand to execute remote stored procedure to do the updates. Is it possible to wrap it all in remote transaction somehow?

mrQQ

Actually, ignore my second question I just tried, and there are "BeginTrans" and "RollbackTrans" and "CommitTrans" functions in ADOConnection object. However, there seems to be a problem - if an error occurs, the transaction seems to be left open, and the created records stay in place, locked aswell. Hm..

Slawek_Guzek

You have TWO separate INDEPENDENT connections:

1. Made by NAV to SQL server
2. Made by you using ADO to remote server.

Each connection maintains its OWN transactions. While you can change properities of yor remote server connection, madre using ADO, and fully decide about its parameters, you cannot do anything with NAV connection. It is estabilished long before your code starts.

What you could try is to read NAV connection token (or whatever it is called - I don't remember now), and try to use the same token (or some of its parameters) when preparing your second connection. I've never done this before, but I've read something some time ago about transaction sharing between different connectiosn. This was regargind transaction sharing beteen two connections made to one MS SQL server, so it will no be a solution for you, but it might give you some other clues that you can do/read from exidting connection.

In my opinion you will not be able to fully mimic SSIS behaviour, as SSIS opens all connections made to different sources using DTC, and this is something what you will not be able to change in NAV client.

Slawek

mrQQ

Thanks, you explained this very well! Sorry if I sounded rude - really didn't mean to

You gave me a great idea, about managing remote transactions myself. Unfortunately, it seems that even if error occurs, NAV doesnt clear automation objects, so remote connection stays hanging.. I've even tried it in if codeunit.run mode, it still won't work. Pity.

Slawek_Guzek

mrQQ wrote:

... However, there seems to be a problem - if an error occurs, the transaction seems to be left open, and the created records stay in place, locked aswell. Hm..

That is going to be a little tricky..

At first try if clearing ADO connection (using CLEAR(ACOvariable)) without submitting CommitTran rollbacks your transaction. It should. If it does then all your ADO variables should be delcared as local in the same proc which is used to make modifications in NAV. When NAV error occures local variables should get cleared, ADO connection closed an remote transactions rolled back.

If this doesn't work probably you need to make global single instance codeunit maintaining all your ADO interface variables and code. Then you should rollbalck any transaction before you start new, and make sure your code commits ADO transaction at the end. Not very sophisticated, and it could leave open remote transaction for LOOOONG time (which may lead to serious troubles), but should work.

Slawek

mrQQ

First option was the first thing i've tried. Unfortunately, NAV doesn't seem to clear local variables

Then I thought about single instance codeunit. Am going to try it now, but I don't really like this approach..

Slawek_Guzek

I see - you tried your code faster than I wrote my response

Slawek

Slawek_Guzek

mrQQ wrote:

NAV doesn't seem to clear local variables

Strange... It does as far as I know.. Where have you defined your variables ? Can you post object and code ?

Slawek

mrQQ

it's supposed to!

    PROCEDURE Test@1101261011(Amount@1101261007 : Decimal) ReturnAmount : Decimal;
    VAR
      POSSetup@1101261000 : Record 17015800;
      AdoConn@1101261002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Connection";
      AdoCommand@1101261008 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000507-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Command";
      AdoParam@1101261009 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{0000050B-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Parameter";
      Mgt@1101261003 : Codeunit 17015800;
      TmpVariant@1101261004 : Variant;
    BEGIN
      GetSetup;

      CREATE(AdoConn);

      AdoConn.ConnectionTimeout(2);
      AdoConn.CommandTimeout(2);
      AdoConn.ConnectionString := Mgt.GetCentralServerConnString;
      AdoConn.Open;
      AdoConn.BeginTrans;

      CREATE(AdoCommand);
      AdoCommand.CommandText := 'Test';
      AdoCommand.CommandType := 4;
      AdoCommand.CommandTimeout := 10;

      AdoParam := AdoCommand.CreateParameter('RETURN_VALUE',3,4);
      AdoCommand.Parameters.Append(AdoParam);

      AdoParam := AdoCommand.CreateParameter('Amount',6,1,38);
      AdoParam.Value := Amount;
      AdoCommand.Parameters.Append(AdoParam);


      AdoParam := AdoCommand.CreateParameter('TestAmount',6,2,38);
      AdoCommand.Parameters.Append(AdoParam);

      TmpVariant := AdoConn;
      AdoCommand.ActiveConnection(TmpVariant);
      AdoCommand.Execute;

      TestAmount := AdoCommand.Parameters.Item(2).Value;

      ERROR('FAIL!');

      AdoConn.CommitTrans;
      AdoConn.Close;

      CLEAR(AdoConn);
      CLEAR(AdoParam);
      CLEAR(AdoCommand);
    END;

unfortunately, seems it doesn

Slawek_Guzek

Have you tried to comment out those three:

ERROR('FAIL!'); 
AdoConn.CommitTrans;
AdoConn.Close;

and check if CLEAR(ADOConn) rollback transaction ? Perhaps you need to add SET XACT_ABORT ON ; in your SQL code (or its equivalent in ADO connection parameters) ?

EDIT
Why you're doing this:

TmpVariant := AdoConn;
AdoCommand.ActiveConnection(TmpVariant);

mrQQ

I have tried, and CLEAR seems to rollback it.

I'm not sure about XACT, will have to read about it.

I'm doing that via variant, because otherwise NAV whines about incompatible data types:(