MSSQL linked server transaction "MSDTC XARMCreate error".

Start
Prev
1
Next
End

ktoss
Topic Author
Offline
User

9 years 7 months ago #722 by ktoss

ktoss created the topic: MSSQL linked server transaction "MSDTC XARMCreate error".

Hello,

Linked server is created like this:

/****** Object:  LinkedServer &#91;PostgreSQL2&#93;    Script Date: 12/13/2010 13:13:59 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'PostgreSQL2', @srvproduct=N'PGNP', @provider=N'PGNP', @datasrc=N'localhost', @provstr=N'PORT=5432;Extended Properties="NESTED_TRANS=ON;"', @catalog=N'postgres'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PostgreSQL2',@useself=N'False',@locallogin=NULL,@rmtuser=N'postgres01',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'PostgreSQL2', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Insert data to table without using transactions works fine:

INSERT INTO &#91;PostgreSQL2&#93;.&#91;postgres&#93;.&#91;synctest&#93;.&#91;measurements&#93;
           (&#91;idmeasurement&#93;
           ,&#91;idmeasuringpoint&#93;
           ,&#91;measuringtype&#93;
           ,&#91;idstatus&#93;
           ,&#91;date&#93;
           ,&#91;value&#93;
           ,&#91;unit&#93;)
     VALUES
           (543
           ,'asdasd'
           ,'ioiosiads'
           ,543
           ,'2010-10-01'
           ,15
           ,'m3')

But this does not work:

BEGIN TRAN TranTest
BEGIN TRY
INSERT INTO &#91;PostgreSQL2&#93;.&#91;postgres&#93;.&#91;synctest&#93;.&#91;measurements&#93;
           (&#91;idmeasurement&#93;
           ,&#91;idmeasuringpoint&#93;
           ,&#91;measuringtype&#93;
           ,&#91;idstatus&#93;
           ,&#91;date&#93;
           ,&#91;value&#93;
           ,&#91;unit&#93;)
     VALUES
           (543
           ,'asdasd'
           ,'ioiosiads'
           ,543
           ,'2010-10-01'
           ,15
           ,'m3')
END TRY
BEGIN CATCH
    ROLLBACK TRAN TranTest
    PRINT 'Error'
    RETURN 
END CATCH

COMMIT TRAN TranTest

The result is:

OLE DB provider "PGNP" for linked server "PostgreSQL2" returned message "MSDTC XARMCreate error".

I can use transaction without any problems with tables located in my mssql server, so transactions work - the problem occurs for PostgreSQL linked server only.

Have I ommited something?

Please Log in or Create an account to join the conversation.

Moderator
Offline
Site Admin

9 years 7 months ago #724 by Moderator

Moderator replied the topic: Re: MSSQL linked server transaction "MSDTC XARMCreate error".

The linked server is trying to use 2-phase commit protocol. The PGNP provider supports the "2PC" protocol utilizing the PostgreSQL durable transactions. Please read chapter 2.6 "Two phase..." in the Developer's Manual (available from http://www.pgoledb.com/index.php?option ... &Itemid=68 ), and perform the configuration steps described in the chapter.

Please Log in or Create an account to join the conversation.

ktoss
Topic Author
Offline
User

9 years 7 months ago #725 by ktoss

ktoss replied the topic: Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Local DTC is enabled (transactions work for mssql). Transactions are enabled in postgresql configuracion file (as described in your pdf), but still the result is the same:

OLE DB provider "PGNP" for linked server "PostgreSQL2" returned message "MSDTC XARMCreate erro

r".

Please Log in or Create an account to join the conversation.

Moderator
Offline
Site Admin

9 years 7 months ago #727 by Moderator

Moderator replied the topic: Re: MSSQL linked server transaction "MSDTC XARMCreate error".

We are working on the issue and will update you soon.

Please Log in or Create an account to join the conversation.

ktoss
Topic Author
Offline
User

9 years 7 months ago #728 by ktoss

ktoss replied the topic: Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Is there any workaround?

Please Log in or Create an account to join the conversation.

Moderator
Offline
Site Admin

9 years 7 months ago #729 by Moderator

Moderator replied the topic: Re: MSSQL linked server transaction "MSDTC XARMCreate error".

I'm afraid there is no workaround. The SQL Server's Linked Server invokes ITransactionJoin interface which is responsible for the Two Phase Commit. The issue occurs because the OLEDB provider was not configured correctly. So the best solution is to configure it...

However, we can send you a test build of the provider with 2PC engine disabled (for testing purposes, to avoid the need to configure the provider). Please let us know.

Please Log in or Create an account to join the conversation.

ktoss
Topic Author
Offline
User

9 years 7 months ago #730 by ktoss

ktoss replied the topic: Re: MSSQL linked server transaction "MSDTC XARMCreate error".

So the best solution is to configure it...

Ok, but how should I do this? I have dtc enabled and it works (tested) and max_prepared_transactions is also set to 100 for postgresql. Is there anything else I sholud configure?

However, we can send you a test build of the provider with 2PC engine disabled (for testing purposes, to avoid the need to configure the provider). Please let us know.

So, please send me this via email: <a href="mailto:michal.milanowski@biatel.com.pl]michal.milanowski@biatel.com.pl[/url], I'll try it and let you know how it works

Please Log in or Create an account to join the conversation.

Moderator
Offline
Site Admin

9 years 7 months ago #731 by Moderator

Moderator replied the topic: Re: MSSQL linked server transaction "MSDTC XARMCreate error".

If you restarted Postgres after changing configuration, it should have worked. I have repeated all the steps from the Developer's Manual on a test computer and everything worked (Postgres 9, SQL Server 2008 R2 x64, Windows 7). Could the issue be related to Windows Firewall? Have you tried to disable the Firewall?

Anyway, we prepared an evaluation build without calls to XA and sent it to you. Please let us know if you will have questions: support at pgoledb dot com.

Please Log in or Create an account to join the conversation.

ktoss
Topic Author
Offline
User

9 years 7 months ago #732 by ktoss

ktoss replied the topic: Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Thanks, I'm going to test this after new year.

Please Log in or Create an account to join the conversation.

Start
Prev
1
Next
End

Time to create page: 0.096 seconds

MSSQL linked server transaction "MSDTC XARMCreate error".

Login Form

Search

VM - Shopping cart

MSSQL linked server transaction &quot;MSDTC XARMCreate error&quot;.

Login Form

Search

VM - Shopping cart

Sign in to your account

MSSQL linked server transaction "MSDTC XARMCreate error".