Auto increment identity of a table and transactions

 Hi,

I am struggling a little with TransactionScope. I haven't gotten it to work yet. I have a dataAccessLibrary. That takes care of the magic mojo related to lightspeed unit of work and saving changes per table.

I have a serviceLogic library that contains validation logic and then calls method(s) in the dataAccess library to complete action on the database.

When it is a single table I am entering data into, I understand that LS can take care of transaction so I don't bother with it.

My issue arises when I am creating an account on my application which required to enter data into multiple tables.

So I start a Transaction scope and then make my respective calls to the dataAccess library to create all the records. If lets say any one of the insert fails then when I catch the exception I rollback(Dispose) the transaction scope.

Currently I am getting error in invoking more than one data access methods. "The partner transaction manager has disabled its support for remote/network transactions."

I need to configure my DTC on both client and db server. :( which I am not sure how.

But while resolving this I noticed the transactionscope was disposing find and the record is not created in the table but my auto increment id gets wasted. Is there a way to save that ID?

Thanks,

You don't say which database you are using, but I think that some databases give the "The partner transaction manager has disabled its support for remote/network transactions" error when you use TransactionScope instead of an ADO.NET transaction.  (I don't have a test case to hand so I may be misremembering here.)  So you might want to try calling IUnitOfWork.BeginTransaction rather than using a TransactionScope and see if that improves matters.

I don't think there's a way to save that autoincrement ID.  (I assume you are talking about a database autoincrement column and not LightSpeed autoincrementing a key table or sequence.)  If the transaction is being rolled back then reclaiming the ID should be handled by the database.  It's not something that LightSpeed can control as far as I know.  Again, if this is an issue of the database not respecting TransactionScope then it's possible the database doesn't think there's a transaction in progress when it allocates the autoincrement ID, so moving to the old ADO.NET transaction model might fix it; but that may depend on your database.

 I think you might be right that transactionscope is not working. I am using sql server 2005. Can I use IUnitOfWork.BeginTransaction outside the data Access layer, different library project?

Thank you.

Hmm, SQL Server 2005 is one of the databases that *does* support TransactionScope, so maybe this is a DTC issue.  Let me ask one of our gurus and we'll get back to you.

 Thanks Ivan, I appreciate it.

Some extra details are... I am using windows 7 to develop... every blog I find in setting up MSDTC is not about windows 7... but did find the place to enable/allow DTC

Component Services> Computers> my Computer> Distributed Transaction Coordinator>Local DTC Properties > Security Tab. In that tab I checked Network DTC Access, Allow Remote Clients, Allow Inbound, Allow Outbound, Mutual Authentication Required, and Enable XA Transactions.

Restarted the DTC service.

I did all of this on my development box, currently my ASP.Net client, Service Layer and Data Access Layer. The sql server 2005 is sitting on a dev server. I am not sure if I would have to do the same on the database server.

Thanks,

Hi,

This may not solve your problem but I had a similar issue recently on Windows 7. It appears that, by default, Windows 7's firewall will block access to MSDTC and cause errors such as "MSDTC is unavailable" and general weird behavior. As I say, this may not be your issue but it's probably worth testing out :-)

The way I discovered it was to go:

Administrative tools -> Component Services -> Expand Component Services -> Expand Computers -> Expand My Computer -> Expand Distributed Transaction Coordinator -> Expand Local DTC -> CLick on Transaction Statistics.

I'm not sure from memory which step triggered it but at one of those steps I got a Windows pop up asking if I would like to add an exclusion to the windows firewall to allow access to MSDTC. After adding that everything went back to working happily and my problem was solved.

Have a go at that - if you don't get the pop up then perhaps try turning off the Windows Firewall temporarily to see if that resolves the issue.

I hope that helps,

John-Daniel Trask

 Hi John,

Thank you for the suggestion. I was really hoping it would work. But I did not get a pop on the Transactions Statistics and then I tried with firewall turned off... still no luck.

I have a question for... for DTC to work correctly do I have to enable it on both client server and database server?

Thanks,

Hi,

Short answer - yes, enable them on both. They should be enabled by default.

Long answer - only if you're enlisting two or more transaction resources at once at which point a database transaction would be escalated to be an MSDTC managed transaction.

If one is currently disabled, enable them and give that a try - hopefully it will help out. Also, temporarily try disabling the firewall on both sides to see if that has any impact.

I hope that helps,

John-Daniel Trask

 Thank you for answering my completely unrelated to lightspeed questions. I will give that a try and hopefully it would work.

Thank you once again. :)