Showing posts with label executing. Show all posts
Showing posts with label executing. 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.