MSSQL linked server transaction "MSDTC XARMCreate error".

More
13 years 4 months ago #722 by ktoss
Hello,

Linked server is created like this:
Code:
/****** 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:
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:
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:
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.

More
13 years 4 months ago #724 by 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 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.

More
13 years 3 months ago #725 by 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:
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.

More
13 years 3 months ago #727 by Moderator
We are working on the issue and will update you soon.

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

More
13 years 3 months ago #728 by ktoss
Is there any workaround?

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

More
13 years 3 months ago #729 by 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.

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

Time to create page: 0.165 seconds
Powered by Kunena Forum