I've been trying to encapsulate this code into a transaction, but I get this error when I try to run it...
SET XACT_ABORT ON
GO
BEGIN TRANSACTION -- Also tried as a DISTRIBUTED TRAN
DELETE
FROM item
INSERT INTO item
([field1], [field2])
SELECT [field1], [field2]
FROM [LINKED_SERVER].[DBASE].[DBO].[ITEM]
If @.@.ERROR <> 0
ROLLBACK TRANSACTION
COMMIT TRANSACTION
GO
SET XACT_ABORT OFF
GO
Server: Msg 7391, Level 16, State 1, Line 2
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].
Thanks in advance for any help you can give me!!!Make sure RPC server did not post any errors in Event Viewer log, and that your previous attempt is not in ROLLBACK state on the linked server.|||I checked the event log and didn't see anything mentioned about that.
Thanks for you thoughts though!|||Do you have a firewall or any port restrictions in place?|||There is a firewall installed, what ports do I need to open up for this to take place?|||See
http://support.microsoft.com/default.aspx?scid=kb;en-us;250367
Also check that the remote server has the servername defined
select @.@.servername
I think this can upset things. It may be lost if you have changed the machine name.|||I'll check that and get back to you...sorry about the delay.|||I checked the servername properties for both servers and they do have names. I will be trying to get the firewall setup for the correct ports to be used with DTC now, but I know this will take a day or two since this i a dedicated server we are using at a hosting provider. So I will let you know how it goes...
Friday, February 17, 2012
DISTRIBUTED TRANSACTION gives off ITransactionJoin error when using SQLOLEDB -
Labels:
code,
database,
distributed,
encapsulate,
error,
itransactionjoin,
microsoft,
mysql,
ongobegin,
oracle,
run,
server,
sql,
sqloledb,
transaction,
xact_abort
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment