Linked servers stored procedures timed out joining

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Linked servers stored procedures timed out joining

Author

Topic

haw001
Starting Member

20 Posts

Posted - 2011-08-04 : 02:30:42

I've recently moved a database from a single sql 2000 server to a split split server arrangement with the new server running sql 2008. The two servers run identical databases structures, with different data.

I am working off the 2008 server which has a linked server to the 2000 server. They are on different domains so not using domain authentication.

I need to collate data from both servers using some legacy stored procedures to collect the data. Originally I had a single stored procedure calling tables on the local and the linked server, but they took ages to run, so to speed things up I've put the stored procedures on each server, and the local server executes the stored procedure for each server.

To collate the data, I have the stored procedure write the resulting data to a temp table on the local server, the resulting table after both stored procedures is run is the complete dataset.

This is where I run into the problem. Running either of the stored procedures from the local server takes about 40 seconds to complete, running them both in sequence causes the query to time out with

quote:
he operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "TR" was unable to begin a distributed transaction.

This is the stored procedure which is on each server:

ALTER PROCEDURE [dbo].[SP_STOCK_ANALYSIS_TR] AS

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON

DECLARE	@Stage TABLE
	(
		Item VARCHAR(25),
		Site VARCHAR(25),
		On_Order INT,
		[Order] VARCHAR(400)
	)

INSERT		@Stage
		(
			Item,
			Site,
			On_Order,
			[Order]
		)
SELECT		RTRIM(POP10110.ITEMNMBR),
		POP10110.LOCNCODE,
		POP10110.QTYUNCMTBASE,
		RTRIM(POP10110.VENDORID) +'-'+RTRIM(SUBSTRING(POP10110.PONUMBER,PATINDEX('%[S,F,M]%',POP10110.PONUMBER),10)) + '-' + CONVERT(VARCHAR, FLOOR(POP10110.QTYUNCMTBASE))
FROM		TR.dbo.POP10110 POP10110
INNER JOIN	TR.dbo.POP10100 POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
WHERE		POP10110.POLNESTA IN (1, 2, 3)
		AND POP10100.POSTATUS NOT IN (4, 5, 6)
		AND LOCNCODE IN ('KP', 'WA')
		AND POP10110.QTYUNCMTBASE <> 0
		-- AND SomeDateColumn BETWEEN ThisDate AND ThatDate

DECLARE @Output TABLE
	(
		RowID INT IDENTITY(0, 1),
		Item VARCHAR(25),
		Site VARCHAR(5),
		On_Order INT,
		Orders VARCHAR(7900)
	)

INSERT		@Output
		(
			Item,
			Site
		)
SELECT DISTINCT	Item,
		Site

FROM		@Stage

DECLARE @Concat VARCHAR(8000),
	@Item VARCHAR(25),
	@Site VARCHAR(5),
	@On_Order INT,
	@RowID INT

SELECT	@RowID = MAX(RowID)
FROM	@Output

WHILE @RowID >= 0
	BEGIN
		SELECT	@Item = Item,
			@Site = Site

		FROM	@Output
		WHERE	RowID = @RowID

		SELECT	@Concat = ISNULL(@Concat + ', ', '') + z.[Order],
			@On_Order = @On_Order + z.On_Order
		FROM	(
				SELECT	On_Order,[Order]
				FROM	@Stage
				WHERE	Item = @Item
					AND Site = @Site
			) z

		UPDATE	@Output
		SET	Orders = @Concat, On_Order = @On_Order
		WHERE	RowID = @RowID

		SELECT	@RowID = @RowID - 1,
			@Concat = NULL,
			@On_Order = 0
	END

-- Output denormalized data

SELECT     TOP 100 PERCENT RTRIM(TR.dbo.IV00101.ITEMNMBR) AS ITEM, RTRIM(TR.dbo.IV00101.ITMSHNAM) AS BARCODE, 
                      RTRIM(TR.dbo.IV00101.ITEMDESC) AS ITEM_DESC, TR.dbo.IV00101.ITEMTYPE, RTRIM(TR.dbo.IV00101.USCATVLS_1) AS CLASS, TR.dbo.IV00101.ABCCODE AS ABCCODE, RTRIM(TR.dbo.IV00101.ITMCLSCD) AS CATEGORY,
                      RTRIM(TR.dbo.IV00101.USCATVLS_6) AS STATUS, TR.dbo.IV00101.CURRCOST, TR.dbo.IV00103.Last_Originating_Cost as FOB,RTRIM(TR.dbo.IV00103.Last_Currency_ID) AS FOB_Curr,
		      TR.dbo.IV00108.UOMPRICE AS SELL, 
                      TR.dbo.IV00102.PRIMVNDR AS PRI_SUPPLIER,TR.dbo.IV00102.LOCNCODE AS LOCATION, dbo.QTY_ON_HAND.QUANTITY, On_Order,Orders, dbo.SALES_HIST.LM_LY, 
                      dbo.SALES_HIST.LM, dbo.SALES_HIST.MBL, dbo.SALES_HIST.M3AGO, dbo.SALES_HIST.M4AGO, dbo.SALES_HIST.M5AGO, dbo.SALES_HIST.M3SUM, 
                      dbo.SALES_HIST.M4SUM, dbo.SALES_HIST.M6SUM, dbo.SALES_HIST.M12SUM, dbo.SALES_HIST.M6AGO
FROM         TR.dbo.IV00102 INNER JOIN
                      TR.dbo.IV00101 ON TR.dbo.IV00102.ITEMNMBR = TR.dbo.IV00101.ITEMNMBR INNER JOIN
                      TR.dbo.IV00108 ON TR.dbo.IV00101.ITEMNMBR = TR.dbo.IV00108.ITEMNMBR AND 
                      TR.dbo.IV00101.PRCLEVEL = TR.dbo.IV00108.PRCLEVEL LEFT OUTER JOIN
                      dbo.SALES_HIST ON TR.dbo.IV00102.ITEMNMBR = dbo.SALES_HIST.ITEMNMBR AND 
                      TR.dbo.IV00102.LOCNCODE = dbo.SALES_HIST.LOCATION LEFT OUTER JOIN
                      RBEACON.dbo.QTY_ON_HAND ON TR.dbo.IV00102.ITEMNMBR = dbo.QTY_ON_HAND.ITEMNMBR AND 
                      TR.dbo.IV00102.LOCNCODE = dbo.QTY_ON_HAND.LOCATION LEFT OUTER JOIN
		      @Output ON TR.dbo.IV00102.LOCNCODE = Site AND 
                      TR.dbo.IV00102.ITEMNMBR = Item LEFT OUTER JOIN
		      TR.dbo.iv00103 ON TR.dbo.IV00102.ITEMNMBR = TR.dbo.IV00103.ITEMNMBR AND
		      TR.dbo.IV00102.PRIMVNDR = TR.dbo.IV00103.VENDORID
WHERE     (TR.dbo.IV00108.FROMQTY = 1) AND (TR.dbo.IV00101.ITEMTYPE IN (1, 3)) AND (TR.dbo.IV00102.LOCNCODE IN ('KP', 'WA'))

Executing it with:

EXEC TR.RBEACON.dbo.SP_STOCK_ANALYSIS_TR

works fine and it exceutes in about 40 secs. Executing the local one results in about the same.

When I try to combine the two with:

ALTER PROCEDURE [dbo].[SP_STOCK_ANALYSIS] AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON

IF OBJECT_ID(N'tempdb..#STOCK_ANALYSIS', N'U') IS NOT NULL
DROP TABLE #STOCK_ANALYSIS

CREATE TABLE #STOCK_ANALYSIS
([ITEM] [varchar](31) NULL,
[BARCODE] [varchar](15) NULL,
[ITEM_DESC] [varchar](101) NULL,
[ITEMTYPE] [smallint] NOT NULL,
[CLASS] [varchar](11) NULL,
[ABCCODE] [smallint] NOT NULL,
[CATEGORY] [varchar](11) NULL,
[STATUS] [varchar](11) NULL,
[CURRCOST] [numeric](19, 5) NOT NULL,
[FOB] [numeric](19, 5) NULL,
[FOB_Curr] [varchar](15) NULL,
[SELL] [numeric](19, 5) NOT NULL,
[PRI_SUPPLIER] [char](15) NOT NULL,
[LOCATION] [char](11) NOT NULL,
[QUANTITY] [int] NULL,
[On_Order] [int] NULL,
[Orders] [varchar](4000) NULL,
[LM_LY] [int] NULL,
[LM] [int] NULL,
[MBL] [int] NULL,
[M3AGO] [int] NULL,
[M4AGO] [int] NULL,
[M5AGO] [int] NULL,
[M3SUM] [int] NULL,
[M4SUM] [int] NULL,
[M6SUM] [int] NULL,
[M12SUM] [int] NULL,
[M6AGO] [int] NULL)

INSERT INTO #STOCK_ANALYSIS
EXEC RBEACON.dbo.SP_STOCK_ANALYSIS_VIC

INSERT INTO #STOCK_ANALYSIS
EXEC TR.RBEACON.dbo.SP_STOCK_ANALYSIS_TR

SELECT * FROM #STOCK_ANALYSIS

it times out and gives the above error.

I'm really stumped. It seems as though the hard work is done, all I'm doing is combining everything into a temporary table.

Any suggestions would be greatly appreciated.

Mark Hawkins
Medalist

lappin
Posting Yak Master

182 Posts

Posted - 2011-08-04 : 05:57:54

Can you confirm you can run the remote procedure in isolation, i.e. can you run "EXEC TR.RBEACON.dbo.SP_STOCK_ANALYSIS_TR" from the local (Non-TR) server?

If it works OK by itself then comment out the local part from SP_STOCK_ANALYSIS:
--INSERT INTO #STOCK_ANALYSIS
--EXEC RBEACON.dbo.SP_STOCK_ANALYSIS_VIC

If procedure works OK it may be a memory issue.
Since the table variable is stored in memory rather than written to disk, perhaps you could try running the remote procedure first.
i.e.
--first remote
INSERT INTO #STOCK_ANALYSIS
EXEC TR.RBEACON.dbo.SP_STOCK_ANALYSIS_TR
--then local
INSERT INTO #STOCK_ANALYSIS
EXEC RBEACON.dbo.SP_STOCK_ANALYSIS_VIC

haw001
Starting Member

20 Posts

Posted - 2011-08-04 : 09:08:58

I can confirm that I can run the remote sp in isolation, and it takes about 20 seconds returning about 9000 records, nothing too taxing.

Commenting out the local sp so that only the remote sp runs does not work, it just runs until it dies with the same error.

If I comment out the remote sp and just leave the local sp it does run, and takes roughly the same as it does when run in isolation. The only difference is that sp runs and displays the results at completion, whereas running it in isolation displays the results as it runs. This makes sense as one way populates a temp table first and the other doesn't.

I thought that maybe NOCOUNT ON would help, but for some reason record counts are still being returned, even though I have it in every sp that is called.

Thanks.

Mark Hawkins
Medalist

haw001
Starting Member

20 Posts

Posted - 2011-08-04 : 09:33:26

Something intersting:

I changed the temp table from #STOCK_ANALYSIS to tempdb.dbo.STOCK_ANALYSIS so that I could see if the table was created in the tempdb and how far it got, and the result is that the temp table populates with the records from the local sp immediately, then nothing happens until it crashes. No records from the remote sp make it to the temp table.

Mark Hawkins
Medalist

lappin
Posting Yak Master

182 Posts

Posted - 2011-08-04 : 11:20:47

For testing: can you modify the remote procedure to save to a permanent table.
Then try in stages, first execute the remote procedure (across the linked server - not inside its own box)
Does this work?
Can you do a select on the table you just filled across the linkage (not in the table's own box)?
If yes to both -
Truncate the table you just filled and modify the SP_STOCK_ANALYSIS procedure to call the remote procedure, then fill your temp table from the physical table the remote proc just filled.
Does this work?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources