MSSQL linked server transaction "MSDTC XARMCreate error".

[ktoss]

Hello,

Linked server is created like this:

Code: Select all

/****** Object:  LinkedServer [PostgreSQL2]    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:

Code: Select all

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


But this does not work:

Code: Select all

BEGIN TRAN TranTest
BEGIN TRY
INSERT INTO [PostgreSQL2].[postgres].[synctest].[measurements]
           ([idmeasurement]
           ,[idmeasuringpoint]
           ,[measuringtype]
           ,[idstatus]
           ,[date]
           ,[value]
           ,[unit])
     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:

Code: Select all

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?

[Moderator]

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.

[ktoss]

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:

Code: Select all

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

r".

[Moderator]

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

[ktoss]

Is there any workaround?

[Moderator]

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.

[ktoss]

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: michal.milanowski@biatel.com.pl, I'll try it and let you know how it works

[Moderator]

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.

[ktoss]

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