- Forum
- Main PGNP Forum
- SQL Server/DTS/SSIS/Linked servers/Replication
- MSSQL linked server transaction "MSDTC XARMCreate error".
MSSQL linked server transaction "MSDTC XARMCreate error".
Linked server is created like this:
/****** 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:
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:
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:
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.
Please Log in or Create an account to join the conversation.
OLE DB provider "PGNP" for linked server "PostgreSQL2" returned message "MSDTC XARMCreate erro
Please Log in or Create an account to join the conversation.
Please Log in or Create an account to join the conversation.
Please Log in or Create an account to join the conversation.
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.
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?So the best solution is to configure it...
So, please send me this via email: <!-- e --><a href="mailto:michal.milanowski@biatel.com.pl]michal.milanowski@biatel.com.pl[/url]<!-- e -->, I'll try it and let you know how it worksHowever, 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.
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.
Please Log in or Create an account to join the conversation.
- Forum
- Main PGNP Forum
- SQL Server/DTS/SSIS/Linked servers/Replication
- MSSQL linked server transaction "MSDTC XARMCreate error".
- You are here:
-
Home
-
Forum
-
Main PGNP Forum
-
SQL Server/DTS/SSIS/Linked servers/Replication
- MSSQL linked server transaction "MSDTC XARMCreate error".