DTC - 'No Active Transaction' returned from linked server

[1DMF]

Hi,

I seem unable to get a distributed transaction to work, it keeps erroring with...

OLE DB provider "SQLNCLI10" for linked server "Members" returned message "No transaction is active.".

Here is the T-SQL

ALTER PROCEDURE [dbo].[spCC_UpdateRAG]
	-- Add the parameters for the stored procedure here
	@Col varchar(10),
	@RAG tinyint,
	@Case_ID int,
	@Officer varchar(50),
	@ACC varchar(3)
AS
BEGIN TRY

    -- start transaction
	BEGIN TRANSACTION	
			
	-- update tables

	-- Audit
	UPDATE Compliance_Audit SET @Col = @RAG WHERE Case_ID = @Case_ID
	
	-- Checker
	UPDATE [Members].[Members_Live].[dbo].Case_Checking SET @Col = @RAG WHERE Case_ID = @Case_ID
	
	-- NBCS
	UPDATE [Members].[Members_Live].[dbo].Business_Register SET Check_Flag = @RAG WHERE Rec_ID = @Case_ID 
	
	-- Insert History
	INSERT INTO Case_Checker_Grades (Case_ID,Grade,Officer,ACC) VALUES (@Case_ID,@RAG,@Officer,@ACC)
	
	-- got this far commit
	COMMIT TRANSACTION
	
	-- return success (true)
	RETURN -1
  
END TRY
BEGIN CATCH
   -- roll back transaction
   ROLLBACK TRANSACTION
   -- return failure (false)
   RETURN 0
END CATCH

I have set up MSDTC, I have successfully ran both DTCPing & DTCTester on both SQL boxes, the firewall rules are in place, linked servers are set up and I can run ordinary queries and T-SQL against them, DTC services seem to be running fine, everything looks as though it should be good to go.

I guess there is something wrong with my transaction?

Your help is appreciated.

Regards,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

Well after a lot of faffing around and reading many, many threads I have it working -> sort of!

1. Even though the instructions say to use 'Mutual Authentication' for the security setting if you are running Windows Server 2008, this is not correct if the two SQL boxes you are trying to link are not on the same domain, if this is the case you have to use 'No Authentication' - which I don't like the sound of but it works!

2. For some reason the windows firewall on one of my boxes didn't have outbound DTC rules, so although I got the DTCPing working by manually adding a rule for the RPC communication on port 135, the source of the request is made on ports 5000+ , so you have to include firewall rules for these ports for the return communications, it is recommended you add at least 20, so I have a rule for ports 5000-5020.

3. It seems to need to use multiple sessions to perform the distributed transaction and I got an error to this affect until I added 'SET XACT_ABORT ON', after the 'BEGIN TRANSACTION' statement. Not sure what this means or does and would appreciate some guidance, perhaps this is related to the below problems I still have?

Now the transaction is running... however, I have a couple of issues.

1. Why is it taking 15-20 seconds to run, this seems way too slow for a stored procedure doing very little even in a distributed SQL server environment, is this normal?
2. - it's ok, worked this one out -> forgot to build dynamic SQL instead of trying to use the @Col directly for column name!

Thanks,
1DMF


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

And when I implemented solution to point 2...

	-- Audit
	SET @SQL = 'UPDATE Compliance_Audit SET ' + @Col + ' = ' + CAST(@RAG AS char(1)) + ' WHERE Case_ID = ' + CAST(@Case_ID AS varchar(15))
	EXEC(@SQL)
		
	-- Checker
	SET @SQL = 'UPDATE [Members].[Members_Live].[dbo].Case_Checking SET ' + @Col + ' = ' + CAST(@RAG AS CHAR(1)) + ' WHERE Case_ID = ' + CAST(@Case_ID AS varchar(15))
	EXEC(@SQL)

The SP now runs in less than a second! solving point 1

Took me a few hours but I got there



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music