RPC to linked server stored procedure causing error 1

[1DMF]

Hi,

I have a linked server and needed to resolve a performance issue with one of my SP's as it was performing a join direct with a linked server table.

Having investigated, it seems the preferred approach is to use a pull method, by calling an SP on the linked table and importing the result to a temp table and then performing the join against the temp table.

After faffing around creating the new SP and re-writing the old one, plus recreating the linked server as I seemed unable to edit the RPC OUT setting needed to call the linked server's SP, I am now getting the following error...

OLE DB provider "SQLNCLI10" for linked server "Members" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

What does this mean? I had a quick search and there seems to be some mention of MSDTC, but before I rush of changing, installing or configuring anything, I'd appreciate some input as to what I might need to do.

Many 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

 

[johnherman]

It's been a decade since I worked on an application with linked servers, but it appears that the remote server "Members" is not allowing the transaction.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[gmmastros]

Are you creating and/or populating the temp table within a transaction? If you are, and if you don't need to, try taking that part out of the transaction.

I don't know if this will work, but it may not be too difficult to test this either.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

I gave up with the SP RPC.

I started to look into MSDTC and there was a whole load of settings I had to change to allow in/out via client requests on both servers, then I still got errors saying there was no transaction available, which apparently is to do with NetBIOS settings and there was other threads telling you how to alter the server's NIC and the DNS with domain suffix.. blah blah.

I got concerned I was opening a can of worms not to mention security risks and maybe even trashing the servers network connectivity.

So I simply rewrote the original SP to do a standard pull query into a temp table from the linked server and then performed the join with the temp table, it went from taking over 12 minutes to run the query to only 6 seconds!

So I don't think any SP performance increase is going to matter.

I am curious why if I have a linked SQL server, I can see it, talk to it and I can query it, why suddenly it's a nightmare and requires set up of this MSDTC and additional networking, just to execute an SP on the same linked server?





"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

 

[gmmastros]

So I simply rewrote the original SP to do a standard pull query into a temp table from the linked server and then performed the join with the temp table

I may not have been clear enough in my previous post, but this is exactly what I was suggesting.

Messing with data is one thing... doing transactions is another. Think of it this way, transactions protect you from lost data in the event of a hardware failure. It's hard enough to get this right with one computer, let alone multiple servers on the network. Basically... anything that requires a transaction on a linked server will also require a distributed transaction coordinator (MSDTC). If you can avoid this without opening yourself up to risking your data, you are better off doing it this way (without the transactions on the remote server).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Hi George,

Are you creating and/or populating the temp table within a transaction? If you are, and if you don't need to, try taking that part out of the transaction.

hmm, I read that as not putting the RPC SP part of the code between the BEGIN section so it isn't part of the first transaction, it's still a TSQL SP, but instead of calling another SP, I just select the data through a normal table query.

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Set @StartDate = coalesce(@StartDate, '2008-01-01') 
	Set @EndDate = coalesce(@EndDate, Convert(char(10), GetDate(), 121))
	
    -- Insert statements for procedure here
    
    -- create temp table for linked server data 
    CREATE TABLE #NBCS (   
		Rec_ID int,
		Prod_Type varchar(30)
    )

	-- populate temp table --
	
	INSERT INTO #NBCS(Rec_ID,Prod_Type)   
	SELECT Rec_ID,Prod_Type
	FROM [Members].[Members_Live].[dbo].[Business_Register]
	WHERE Adv_Memno LIKE @Adv_MemNo + '%'
	
	-- EXEC [Members].[Members_Live].[dbo].spADM_ProductTypes @Adv_MemNo 
	
	-- execute join
	SELECT ContactID,[CDate Visited] AS CheckDate, [Client Name] AS CNames, Rating AS IG, Prod_Type,
	
	Case When
			(Checks IS NULL)
			Then 1
			Else Checks 
			End	
	AS Checks			
	,		
	Case When			
			(Final_Rating IS NULL)
			Then ' '
			Else CAST(Final_Rating AS Char(1))
			End
	AS FG,
	
			Case When
			(Case_Check_Hist IS NULL)
			Then 'N/A'
			Else Case_Check_Hist 
			End
	AS Grades, 
			Case When
			(Case_Class IS NULL)
			Then 'N/A'
			Else Case_Class 
			End
	AS Classes
	
	FROM Compliance_Audit ca JOIN #NBCS b
	ON b.Rec_ID = ca.Case_ID AND ca.Rating > 0 AND [CDate Visited] IS NOT NULL
	LEFT JOIN (SELECT Case_ID, Count(Case_ID) AS Checks FROM Case_Checker_Grades GROUP BY Case_ID) g
	ON b.Rec_ID = g.Case_ID
	WHERE ([CDate Visited] BETWEEN @StartDate AND @EndDate)
	ORDER BY [CDate Visited] DESC

	DROP TABLE #NBCS
	
END

I appreciate that all transactions in the chain must succeed for a successful transaction, but the remote linked server data query if it fails would still kill this transaction wouldn't it? It's still inside a transaction, how would I "...try taking that part out of the transaction." ?

I guess what you meant was don't chain transactions together across servers unless you really need to in a proper DDBMS system?




"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