MSSQL linked server transaction "MSDTC XARMCreate error".

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

MSSQL linked server transaction "MSDTC XARMCreate error".

Postby ktoss » Mon Dec 13, 2010 5:20 am

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?
ktoss
 
Posts: 5
Joined: Mon Dec 13, 2010 5:12 am

Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Postby Moderator » Mon Dec 13, 2010 11:50 pm

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.
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm

Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Postby ktoss » Fri Dec 17, 2010 1:04 am

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".
ktoss
 
Posts: 5
Joined: Mon Dec 13, 2010 5:12 am

Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Postby Moderator » Mon Dec 20, 2010 8:14 am

We are working on the issue and will update you soon.
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm

Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Postby ktoss » Wed Dec 22, 2010 8:05 am

Is there any workaround?
ktoss
 
Posts: 5
Joined: Mon Dec 13, 2010 5:12 am

Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Postby Moderator » Thu Dec 23, 2010 1:03 am

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.
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm

Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Postby ktoss » Thu Dec 23, 2010 7:33 am

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
ktoss
 
Posts: 5
Joined: Mon Dec 13, 2010 5:12 am

Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Postby Moderator » Fri Dec 24, 2010 9:55 pm

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.
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm

Re: MSSQL linked server transaction "MSDTC XARMCreate error".

Postby ktoss » Sat Dec 25, 2010 11:59 am

Thanks, I'm going to test this after new year.
ktoss
 
Posts: 5
Joined: Mon Dec 13, 2010 5:12 am


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 5 guests

cron