Distributed Transaction Fails and stops MSSQL Service

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

Distributed Transaction Fails and stops MSSQL Service

Postby GuillermoDavalos » Wed Feb 27, 2013 3:56 pm

Hello,

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:

http://www.pgoledb.com/forum/viewtopic.php?f=4&t=467

The query looks something like this:

SET XACT_ABORT ON

BEGIN TRY
BEGIN DISTRIBUTED TRANSACTION

INSERT INTO [PGlinkedserver].[db].[schema].[table](
code,
id1,
id2,
col4,
col5,
col6,
col7
)VALUES(
'12312',
5009,
123123,
'val4',
'val5',
'val6',
'val7'
)
PRINT 'preCOMMIT'
COMMIT TRANSACTION
PRINT 'postCOMMIT'
END TRY
BEGIN CATCH
-- Rollback if called by sql job script, otherwise raise error and let caller sp to roll back
PRINT ERROR_MESSAGE()
IF @@TRANCOUNT = 1
BEGIN
ROLLBACK TRANSACTION
END
PRINT 'Error'
PRINT ERROR_MESSAGE()
RETURN
END CATCH

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.

Best Regards.
GuillermoDavalos
 
Posts: 1
Joined: Wed Feb 27, 2013 2:53 pm

Re: Distributed Transaction Fails and stops MSSQL Service

Postby Moderator » Mon Mar 04, 2013 10:08 pm

Could you send the table DDL to Support?

Have you tried to execute the statement without transaction?

Thank you!
User avatar
Moderator
Site Admin
 
Posts: 301
Joined: Wed Oct 29, 2008 11:27 pm


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

Who is online

Users browsing this forum: No registered users and 1 guest

cron