I'm trying to get a distributed transaction to work from a MSSQL Server 2008 R2 instance on Windows 7 64 bit to a Postgres 8.4 instance in Ubuntu with a linked server using the PGNP provider. My problem is basically the same one discussed here:
The query looks something like this:
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO [PGlinkedserver].[db].[schema].[table](
-- Rollback if called by sql job script, otherwise raise error and let caller sp to roll back
IF @@TRANCOUNT = 1
I followed the instructions in the developer PDF to create the linked server and also did all the steps in the developer PDF for two phase commit protocol enabling with no success (DTC setup, REGISTRY KEYS, postgres.config file).
When the transaction is run it hangs for around 20 seconds stops the MSSQL Server local Service and shows the following error:
Msg 2, Level 20, State 0, Line 0
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
It showed other XA transactions related errors before doing the two phase commit protocol steps but now is that one that shows which is due to the MSSQL Server Service being stopped.
Sometimes after a few MSSQL server service restarts the first try works but then all subsequent tries fail again and break the service, looks like the connection is not being closed properly or something.
Please let me know if you know how to fix this. Thanks in advance.