Problems with FOR UPDATE triggers

[jcrivera]

Greetings...

I have two SQL 2000 servers, A and B, being Svr A in our local domain, and Svr B is in a DMZ (i.e. in the internet). Svr A can see Svr B, but for security reasons, Svr B cannot see Svr A. This is OK.

From Svr A, I'm trying to make a distributed (remote) UPDATE being the source table in Svr B and the target in Svr A:

UPDATE A
SET A.Field1 = B.Field1
FROM SvrA.dbA.dbo.tblA A INNER JOIN SvrB.dbB.dbo.tblB B
ON A.PK1 = B.PK1
WHERE blablablabla....

I get the following result:
Server: Msg 7391, Level 16, State 1, Line 1

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. ]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].


After too much investigation, I've discovered so far that the problem is due to the combination of several facts that are present at the same time:
(1) The SQL Server version is 2000 (I don't have this problem with version 7)
(2) The target table contains FOR UPDATE triggers (this doesn't occur with FOR INSERT triggers)
(3) One of the servers is in the DMZ (if both servers were at the local network, I wouldn't have this prob).

Is there any way in which I can solve this situation? Am I posting this problem in the correct forum? Please advise.

Thanks!

J.C.

 

[fluteplr]

Do you ahve the distributed transaction service running on both servers?

 

[jcrivera]

Yes indeed...
If you read again (at the end of my post) the three problems I found so far (DMZ, update triggers, sql version) I'm stating that if I remove ANY of these three variables, the UPDATE statment works without any problems. The problem arises when the 3 variables are present at the same time.

Of course, if the DTC was absent I wouldn't be able to run this update at all.

J.C.

 

[zorgster]

We've just had this same error message appear. DTS is started on both sides. The database is replicating ok. But we can only run SELECT statements from our New York office. In London it works fine. UPDATE DELETE and INSERT all gave the 7391 error. The table in question also has triggers. And it is SQL 2000.

But even tho it is replicating... and so on...

ITransactionJoin is the key. Some OLE DB providers support this and some don't... some only support ITransactionLocal... somewhere else i saw someone recommend looking at the properties of the Linked Servers...

LS_LONDON in New York was set up as a SQL data connection.
LS_NEWYORK in London however seemed to think it was a Jet 4.0 OLE DB Provider. We are not quite sure how that changed... as no changes were made and it was working up to a point. And yet some functionality still worked (replication - does that rely on the LS_xxx in Linked Servers?) ...

I would suggest checking the properties of the Linked Servers ... Srv A and B ... to see if one is perhaps set to use the Jet 4.0 OLE provider to connect to your SQL server. (and how is that possible?).