Linked server Trigger Issue

• balajikannan2005

  Newbie

  Points: 5

  More actions

  February 5, 2009 at 6:07 am

  #218240

  Dear all ,

  I have established a Link server from Machine A to Machne B

  where Machine A - Having sql server 2000 ( also having the sql server 2005 instance in it)

  and Machine B - Having Sql server 2005

  I wrote a Trigger in Machine A - in [DB] Database .. as follows

  Create trigger TR_CUS on customer after insert

  as

  begin

  declare @memberId int

  declare @FirstName varchar(100)

  declare @Lastname varchar(100)

  select @MemberId = MemberId, @FirstName = FirstName,@Lastname = Lastname from Inserted

  insert into [Machine B].[DB].dbo.customer (memberId,FirstName,LastName)

  values

  (@memberId,@FirstName,@Lastname)

  end

  There is no issues so far

  when i tried to a row in [Machine A] Customer it should insert in [Machine B] also

  instaed its showing a error message of

  "The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

  [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]"

  i have done all the thngs related DTC issue but its still remains the same .

  Can any one pls give some suggestion to me.

  Thanks

  Balaji K

• Jack Corbett

  SSC Guru

  Points: 184397

  More actions

  February 5, 2009 at 8:05 am

  #938877

  First I am going to suggest that you read this article[/url] as it addresses some things you are doing in your trigger that are not best practices.

  Secondly, can you manually insert across the linked server? Is MSDTC running on BOTH servers?

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question