distributed transaction

Question

distributed transaction

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 1

Yes, SQLNCLI10.

When ever there is an error the transaction is simply being rolled back and getting the error I mentioned in the previous post.

NO hold on the code.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Which error severity are you getting? If above 20, it could jump off the CATCH block.

-- Gianluca Sartori

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 3

sql_novice_2007

Hall of Fame

Points: 3307

March 14, 2011 at 12:00 pm

#1298502

Level 11

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Have you tried adding some PRINT commands inside the CATCH block to see if they get executed? Level 11 should be handled by CATCH blocks.

-- Gianluca Sartori

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 5

yes, If I add a print stmt before each tranasaction and then if the third sql stmt fails... only the first two PRINT stmts are being printed and they are followed with the severe error occurred message.

Thanks.

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 6

sql_novice_2007

Hall of Fame

Points: 3307

March 14, 2011 at 1:37 pm

#1298552

any ideas?

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 7

sql_2005_fan

SSCommitted

Points: 1871

March 14, 2011 at 5:55 pm

#1298630

Any suggestions?

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Have you tried issuing the statements one at a time? Do they work that way?

-- Gianluca Sartori

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 9

sql_novice_2007

Hall of Fame

Points: 3307

March 15, 2011 at 7:29 am

#1298834

No. I want them to be in a nested transaction.

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

I understand what you're after. I just want to know if the statements work when run one at a time.

If they work, we have to focus on the distributed transaction, otherwise we will have to examine the offending statement.

-- Gianluca Sartori

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 11

sql_novice_2007

Hall of Fame

Points: 3307

March 15, 2011 at 8:15 am

#1298863

No, its not working with even one transaction.

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

spaghettidba

SSC Guru

Points: 105732

March 15, 2011 at 8:29 am

#1298878

Which statement is failing?

-- Gianluca Sartori

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 13

sql_novice_2007

Hall of Fame

Points: 3307

March 15, 2011 at 8:32 am

#1298880

Insert statement.

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

INSERT INTO srvr2.db2.dbo.tbl_a (col1, col2)

VALUES (9999, 8885)

If you open a new SSMS window and run this, it fails, right?

OK, so, let's see how you set up the linked server. Can you post the linked server script?

Also, we should check how the provider is set up. Can you post the provider configuration? You should find it under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers.

Another thing to ckeck: is MSDTC running and configured properly?

-- Gianluca Sartori

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 15

Yes, the insert fails when I run the script in SSMS, becuase of the datatype. col2 is a bit.

I am intentionally inserting these values to insert the error message.

Following is the links server setup script.

EXEC

master.dbo.sp_addlinkedserver @server = N'srvr2', @srvproduct=N'SQL Server'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC

master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'srvr2',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'data access', @optvalue=N'true'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'dist', @optvalue=N'false'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'pub', @optvalue=N'false'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'rpc', @optvalue=N'true'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'rpc out', @optvalue=N'true'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'sub', @optvalue=N'false'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'collation name', @optvalue=null

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC

master.dbo.sp_serveroption @server=N'srvr2', @optname=N'remote proc transaction promotion', @optvalue=N'true'

GO

I dont see the provider setup under the location you have mentioned.

MSDTC is configured and running properly, If transaction run with no error then the data is getting updated/inserted/deleted on the linked server(srv2).

Thanks.