Showing posts with label xact_abort. Show all posts
Showing posts with label xact_abort. Show all posts

Friday, February 17, 2012

distributed transaction problem on remote server

I am getting a problem in executing this distributed query



CREATE PROCEDURE [sp_ec2fetch] AS



set XACT_ABORT on

begin distributed tran



select * from OPENDATASOURCE(

'SQLOLEDB',

'Data Source=[ip];User ID=[uid];Password=[pass]'

).dml.dbo.act where code like '4010101000'

commit tran

GO



When I run it with a local LAN IP it works fine, but when I use a public ip my analyzer hangs and doesnt give me any output. I am using a dedicated dialup 64kbps connection. I have tried this query without begin distributed trans and commit trans and it worked fine, may be there are extra overheads with transactions, if so let me know



As an alternate I have tried registering server with sp_addlinkedserver and then executing a query with direct server reference, but had the same problem with it.Ensure MSDTC is started on both the servers.

Distributed Transaction Help

I try to use disrtibuted transaction. First,I use MSDASQL successfull:

set XACT_ABORT on
set ANSI_NULL_DFLT_ON on
set ANSI_WARNINGS on
BEGIN DISTRIBUTED TRANSACTION
select * from OPENDATASOURCE('MSDASQL','DRIVER={SQL Server};SERVER=192.168.2.1;UID=sa;PWD=PASSWORD;'). pubs.dbo.jobs
commit tran

Then I try to use SQLOLEDB, But failure:

set XACT_ABORT on
set ANSI_NULL_DFLT_ON on
set ANSI_WARNINGS on
BEGIN DISTRIBUTED TRANSACTION
select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.2.1;User ID=sa;Password=PASSWORD').pubs.dbo.jobs
commit tran

I get the following error message:
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].

but I run the code use SQLOLEDB without DISTRIBUTED TRANSACTION success:

select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.2.1;User ID=sa;Password=PASSWORD').pubs.dbo.jobs

WHY? Please help me!is 192.168.2.1 the server you are executing from? You can't do this to the same instance of sql server.|||Originally posted by nigelrivett
is 192.168.2.1 the server you are executing from? You can't do this to the same instance of sql server.

Thanks nigelrivett! I executing the code each server,but get th same result!|||WHO KNOW WHY? WHO CAN HELP ME?|||I assume dtc is running and rpc allowed.
Is there a firewall between the servers.
Are they on the same domain.

I would suggest you post the message again.

If you do a search you will find a number of possibilities but there are a lot of things that can affect this.|||Maybe we can try downloading the DTCPing.exe file from the website below and executing DTCPing on both servers. The log file which will then be displayed will tell u what is wrong with the connection.

That's what i did and i found out my RPC was something wrong.
My error was "There are no more end points available from the end point mapper." Just that i don't understand what this error means.

http://support.microsoft.com/default.aspx?scid=kb;en-us;306843

DISTRIBUTED TRANSACTION gives off ITransactionJoin error when using SQLOLEDB -

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