locked
TransactionScope with sql and msmq RRS feed

  • Question

  • Is it possible to use transactionscope and in the {} to use sql and msmq?

    msmq is the datasource and the sql is the target. I wan't it to be transactional

    Could't find a sample for it .

    Is that possible ?

     

    Thanks

    Avi

    Monday, July 3, 2006 7:39 AM

Answers

  • Hi,

    Can you try moving "MessageQueue messageQueue = new MessageQueue(qPath);" inside the TransactionScope scope? That should do the trick.

    Cheers!

    Tuesday, July 4, 2006 4:25 PM

All replies

  • Well, Still didn't find samples on it but tried to use a sipmle sql insert while using a transactionscope but getting errors.

    Had few problems with the "dtc not enabled" . I enabled the remote transaction both in the clien(xp sp2) and in the server (2003 with sql 2000). checked the security boxs in the component service but now getting "The transaction has already been implicitly or explicitly committed or aborted" whenever I open the sqlConnection.

    Tried the reghistry key "TurnOffRpcSecurity" set to 1 but still o good.

    My code is simple.

    using(TransactionScope ts = new TransactionScope())

    {

    using (SqlConnection connection2 = new SqlConnection(sqlConn))

    {

    // The transaction is escalated to a full distributed

    // transaction when connection2 is opened.

    connection2.Open();

    }

    Monday, July 3, 2006 1:31 PM
  • Hey Avi_harush,

    Does it immediately throw saying "The transaction ahs already been implicitly or explicitly committed or aborted", or does it hang for a little bit.  If it hangs for a bit, then it sounds to me like a firewall issue.  What would be happening is that both msdtc instances are trying to communicate so that the Transaction can be "moved" between machines.  Without being allowed through the firewall the Transaction times out and aborts during these attempts.  On both machines you need to ensure that msdtc.exe is allowed through the firewall and that traffic on tcp port 135 is allowed through the firewall.  Port 135 is used by the RPC endpoint mapper.

    Let me know if you still have issues getting this to work,
    Miguel

    Monday, July 3, 2006 4:19 PM
  • Hi Miguel

    Well, Thanks for the reply . Will check it out.

    At the moment, I took the code to the server and now it works fine.

    Now I have another problem.

    Inside the transaction scope, I pull data from msmq andf save it to the db.

    Lets say I have 10 items in the queue. I do a loop of 5 steps in the scope.

    At the end of the loop and the scope , It commits the transaction and now I print the rest of the data in the queue. 6 to 10.

    The problem is that if , from some reason, there is an exception in the scope,The transaction is rolled back only for the sql. I see that because after the "scope" failed it prints 6-10 while it should have rolled back to item 1.

    How is that ?

    It would be great if you could post a sample for this

     

    Thanks

    Avi

    Monday, July 3, 2006 5:27 PM
  • Its not working even only for the msmq

    here is my code

     

    public static void getData()

    {

    MessageQueue messageQueue = new MessageQueue(qPath);

    try

    {

    using (TransactionScope tScope = new TransactionScope())

    {

    for (int i = 0; i < 5; i++)

    {

    Message m = messageQueue.Receive();

    Console.WriteLine(m.Label);

    }

    //throw new Exception("Error \n");

    Console.WriteLine("Finished the scope \n\n");

    tScope.Complete();

    }

    }

    catch (Exception er)

    {

    Console.WriteLine("Error l = " + er.Message);

    }

    for (int i = 0; i < 10; i++)

    {

    Message m = messageQueue.Receive();

    if(m != null)

    Console.WriteLine(m.Label);

    }

    }

     

    If I unmark the "Throw" , The transaction should be aborted and I should see 0 to 9 printed on the console. At the moment, no matter what, it prints 5-9 after the transaction (commited or aborted)

    Tuesday, July 4, 2006 5:45 AM
  • Hi,

    Can you try moving "MessageQueue messageQueue = new MessageQueue(qPath);" inside the TransactionScope scope? That should do the trick.

    Cheers!

    Tuesday, July 4, 2006 4:25 PM
  • Finally found the problem.

    It seems that if I want the queue to be controlled by the scope,I need to declare the constructor with bool flag for Transactional "new MessageQueue(pathe,true)"

    That solved the problem.

    By the way, From what I have been reading . Tscope don't work with Hibernate.

    Is that true ?

    Avi

    Tuesday, July 4, 2006 6:44 PM
  • TransactionScope and System.Transactions should work with System Hibernate.
    Wednesday, July 5, 2006 5:53 AM
  • Monday, July 2, 2012 1:23 PM