Link to home
Start Free TrialLog in
Avatar of holemania
holemania

asked on

VB.NET - SQL Triggers with linked server

Hello experts,

Not sure if this issue is from my vb.net application or the trigger within SQL Server 2005.  I have 2 SQL Server 2005 in which one has a update/insert trigger that will replicate some data from one server to the other and various databases within those 2 SQL Server.

Prior to us moving to 2 SQL Server, I had never had this issue.  However, there's a need to split some databases up due to size and performance.  Whenever I use my application to create new records or update, I would get the following error.

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" returned message "No transaction is active.".

Upon doing some research, I found that it might have to do with MSTDC.  I turned that on for both SQL Server with no athentication and it seems to be working.  However, it is sparodic in that it'll work fine one moment and the next I'm getting the above error again.

My VB.Net program only connect to one SQL Server which is the local server (Never had this issue until the remote SQL Server came into play).  That local SQL Server has a trigger that will either insert/update with the remote SQL Server.  So I am assuming that the error might be coming from the trigger.  However, I'm not sure what else needs to be look at.  Can anyone assist?
ASKER CERTIFIED SOLUTION
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of holemania
holemania

ASKER

Thanks.  Went back and made sure that MSTDC was configure correctly.  Issue seems to go away now.
The posted solution wasn't the exact solution, but helped in troubleshooting the problem.