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: 279
Joined: Wed Oct 29, 2008 11:27 pm

ljyu <a href=http://eltonder.com/CelineBags.php><strong>Celi

Postby ljtbkqzsvyrg » Sun Apr 20, 2014 7:21 am

I put his shoes is a positive, even every tiny wrinkles. Bottega Veneta Outlet Online Bottega Veneta Soldes CCNA expects to further improve operational control and reduce exposure to risk, while decreasing costs and improving productivity. Bottega Veneta Wallet Herve Leger Swimwear Other than flaunting cheap Ugg Boots, the reason behind this is to make the collections customer friendly. Bottega Veneta Wallet Sale Celine Borse Prezzi He dabbled in silver mining and, by 1971, had bought the 1891 Brand Building for less than $170,000. Bottega Veneta Wallet , Some become clear later, still often be some early exhaust emotional sting, become the memory cannot be changed landscape, chest pain forever, ever gap. Celine Tote Bag Bottega Veneta Wallet Sale I was so helpless, squatting on the floor crying. Bottega Veneta Online Shop <a href=http://annabegins.co.uk/CelineBags.php><strong>Celine Outlet</strong></a> I am young, no culture, to change your environment. Herve Leger Dress <a href=http://eltonder.com/HerveLeger.php><strong>Herve Leger Bathing Suit</strong></a> I looked at the table, just five more days, has gray. Bottega Veneta Handbags
ltbwizuk http://www.cdisa.ca/CelineBags.php wdprtsfu
ljtbkqzsvyrg
 
Posts: 24
Joined: Fri Apr 18, 2014 7:16 pm

sdzx <a href=http://www.samurai-kommunikation.de/CelineTasch

Postby ljtbkqzsvyrg » Sun Apr 20, 2014 7:22 am

I found these to farming, face people, mostly young young man, was not one, in which there are a number of women, particularly conspicuous, some women are beautiful, provoked a plough frequently peeking. Bottega Veneta Price Sac Celine Vintage One of the areas that metrosexuality covers is the formal shirts department. Bottega Veneta Sacs Celine The developer said an event space was the first thing he and Heinz thought of when they walked into the building, which was formerly home to the Standard Wilson Glass Co. Herve Leger Swimsuit Herve Leger Outlet Y las mujeres se quejan continuamente, y creo firmemente que los hombres ya sea A. Herve Leger Dress Buy a copybook for calligraphy, individuality in the font also can not cover up, not a good phenomenon, because the word as its people. Bottega Veneta Paris Bottega Veneta Wallet Sale I become more and more frustrated, the teacher repeatedly reprimanded so i have a rebellious, very wronged. Herve Leger Swimwear <a href=http://eltonder.com/BottegaVeneta.php><strong>Bottega Veneta Handbags</strong></a> I have not seen for a long time, demon, i don't know if she went there, i can't find her, but i will still look forward to the day when, what, and i first met, just appear in my eyes. Herve Leger Swimwear <a href=http://www.studiogo.co.uk/HerveLeger.php><strong>Herve Leger</strong></a> The same intriguing prospects will be there next year in the draft. Celine Wallet
ecklnvxg http://www.mauijimhorsetrials.org/HerveLeger.php bditpslt
ljtbkqzsvyrg
 
Posts: 24
Joined: Fri Apr 18, 2014 7:16 pm


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

Who is online

Users browsing this forum: No registered users and 3 guests

cron