0

I have a situation where when I update user I also have to keep track of all changes made to the user - sort of like activity log for the user table. This activity log is located in a different database on the same DB server. But when I wrap these 2 DB calls in a transaction scope I get an error.

So I was wondering if there is a way to bypass/resolve this issue?

Marko
  • 12,543
  • 10
  • 48
  • 58
  • 2
    What error? What version of SQL Server? – Joe Feb 08 '13 at 13:51
  • Do you have DTC configured and running? http://technet.microsoft.com/en-us/library/dd337629(v=ws.10).aspx – Pete Feb 08 '13 at 13:53
  • The error I get is: "The partner transaction manager has disabled its support for remote/network transactions." This doesn't say much to me be but when I use TransactionScope with multiple queries on the same database it works fine so my conclusion was that it's due to having the scope span across 2 databases. I might be wrong. We are using 2008 SQL Server. And I'm not sure about DTC I have to check with our sys admin guy... – Marko Feb 08 '13 at 14:12

4 Answers4

2

Care to share what exactly does the error say? Anyway from the top of my head this usecase should be supported by TransactionScope. As far as I remember though the transaction will be promoted to a distributed transaction and that needs the Distrbuted Transaction Coordinator service (MSDTC) to be installed and running on your machine

EDIT: seeing the error message it seems the 2 databases are sitting on different machines so the distributed transaction needs to be done/synchronized over the network. That means that MSDTC is installed but not properly configured for this use case. Look it up in the documentation and have a chat with your sys admin.

Adrian Zanescu
  • 7,907
  • 6
  • 35
  • 53
  • Hmmm... Reading these articles: http://stackoverflow.com/questions/761031/how-do-you-get-around-multiple-database-connections-inside-a-transactionscope-if http://nullablecode.com/2011/02/how-to-avoid-unnecessary-msdtc-escalations/ it seems that I don't need MSDTC when I have a single SQL server with multiple database. It seems like I need to find a way to persist the same connection across all database calls. As it is in my project now I create a separate connection for each database call. – Marko Feb 08 '13 at 15:50
1

If you are using SQL Server 2008 you can wrap connections to the same SQL Server in a transaction scope without MSDTC, provided you use the same connection string for each connection.

To use the same connection string, you will need to specify the database name (for at least one of your databases) explicitly in your queries, e.g.

SELECT ... FROM db1..Table1 ...

SELECT ... FROM db2..Table2 ...
Joe
  • 122,218
  • 32
  • 205
  • 338
0

You need to ensure that MSDTC is installed and configured. By configured, I mean you need to enable network DTC acccess.

This can get you started: http://technet.microsoft.com/en-us/library/cc753866(v=ws.10).aspx

Pete
  • 6,585
  • 5
  • 43
  • 69
-3

I don´t believe this possible since a transaction is associated with only one connection. What you need to achieve this behavior is to have a parallel procedure (i.e a concurrent thread) that keeps reading the log database each n seconds

Rodrigo Vedovato
  • 1,008
  • 6
  • 11