Solved

SQL Server 2005 LInked Server Problem

Posted on 2011-03-04
7
452 Views
Last Modified: 2012-05-11
I have a two-node sql server 2005 cluster (sql03v) and a single node sql server 2005 (eng01). eng01 is set up as a linked server on sql03v. I have a stored procedure on sql03v that when I run the individual statements one at a time works as expected. It reads data from a table on eng01, populates a temp table on sql03v, inserts to an archive table on sql03v, then markes one field in the table on eng01 from N to Y to show that it has processed. However, when I execute the stored procedure (exec aer_mts_getdata) I was getting the following error:

ole db provider "sqlncli" for linked server returned message "no transaction is active.".

I researched that and reconfigured the msdtc on both servers to allow Network DTC Access, Allow Remote Clients, Allow Remote Administration, Allow Inbound, Allow Outbound, No Authentication Required, Enable Transaction IP transactions and to Enable XA Transactions. I have updated both servers with latest Windows Updates and both sql servers are upgraded to service pack 4.

Now when I execute the sp I get this error:

OLE DB provider "SQLNCLI" for linked server "ENG01" returned message "Cannot start more transactions on this session.".

I'm not sure what else is wrong. Any help resolving this problem would be greatly appreciated.
USE [fin_sim]
GO
/****** Object:  StoredProcedure [dbo].[AER_MTS_GETDATA]    Script Date: 03/04/2011 08:36:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AER_MTS_GETDATA]
AS
    BEGIN
        SET NOCOUNT ON;
        
        BEGIN TRY
            
            BEGIN TRANSACTION
            
            CREATE TABLE #RowsToProcess
                (  [TRANSACTION_CODE] [varchar](6) NULL
                  ,[COMPANY_CODE] [varchar](10) NOT NULL DEFAULT('1')
                  ,[FACTORY] [varchar](2) NOT NULL DEFAULT('')
                  ,[JOB_NUMBER] [varchar](10) NOT NULL DEFAULT('')
                  ,[PROCESS_STAGE] [varchar](6) NOT NULL DEFAULT('1')
                  ,[RECIPE_LINE_NUMBER] [smallint] NOT NULL DEFAULT((0))
                  ,[WAREHOUSE] [varchar](2) NOT NULL DEFAULT('')
                  ,[TAG_ID] [varchar](30) NULL
                  ,[BIN_LOCATION] [varchar](15) NOT NULL DEFAULT('')
                  ,[QTY_1] [numeric](20,6) NOT NULL DEFAULT((0))
                  ,[UOM_1] [varchar](4) NULL
                  ,[TRANSACTION_DATE] [varchar](30) NULL
                  ,[COMMENT_LINE] [text] NULL
                  ,[PROCESSED] [varchar](1) NOT NULL DEFAULT('')
                  ,[REPROCESS] [varchar](1) NOT NULL DEFAULT('')
                  ,[PROCESSED_DATE] [datetime] NOT NULL DEFAULT('17-NOV-1858')
                  ,[HIST_OR_ERR] [varchar](30) NULL
                  ,[PART_CODE] [varchar](15) NULL
                  ,[ID] [int] NULL
                  ,[MTS_BATCH_ID] [varchar](10) NULL
                  ,[MTS_LINE] [varchar](2) NULL
                ) ;
            
/*
CAPTURE THE CURRENT RECORDS NOT PROCESSED BY MARKING THEM ALL WITH A 'T'
*/
            
            UPDATE [SDWINENG01].[ROSS_INTERFACE].[dbo].BATCH_HEADER
            SET ROSS_PROCESSED = 'T'
            WHERE ROSS_PROCESSED = 'N'
                AND DATETIME_DUMP BETWEEN '02-14-2011' AND '02-15-2011';
            
/* POPULATE TEMP TABLE #RowsToProcess WITH DATA FROM MTS MARKED WITH A 'T'.
THIS WILL SET THE WHERE CLAUSE TO ONLY WHERE H.ROSS_PROCESSED='T' AND GREATER
THAN THE DATE WE IMPLEMENT THIS TO PRODUCTION. ANOTHER OPTION WOULD BE
TO AUTOMATICALLY CHANGE ALL THE ALREADY-PROCESSED ROSS_PROCESSED FIELDS
TO Y WHEN WE GO LIVE, THEN WE CAN DROP THE DATE LIMITATION */
            
            INSERT
            INTO [#RowsToProcess]
                ([TRANSACTION_CODE]
                  ,[COMPANY_CODE]
                  ,[FACTORY]
                  ,[JOB_NUMBER]
                  ,[PROCESS_STAGE]
                  ,[RECIPE_LINE_NUMBER]
                  ,[WAREHOUSE]
                  ,[TAG_ID]
                  ,[BIN_LOCATION]
                  ,[QTY_1]
                  ,[UOM_1]
                  ,[TRANSACTION_DATE]
                  ,[COMMENT_LINE]
                  ,[PROCESSED]
                  ,[REPROCESS]
                  ,[PROCESSED_DATE]
                  ,[HIST_OR_ERR]
                  ,[PART_CODE]
                  ,[ID]
                  ,[MTS_BATCH_ID]
                  ,[MTS_LINE]
                )
                (SELECT 'PMPROC' AS TRANSACTION_CODE
                  ,'1' AS COMPANY_CODE
                  ,H.FACTORY
                  ,H.JOB_NUMBER
                  ,'1' AS PROCESS_STAGE
                  ,RL.RECIPE_LINE_NUMBER
                  ,RL.WAREHOUSE
                  ,'' AS TAG_ID
                  ,'' AS BIN_LOCATION
                  ,I.INGR_ACTUAL AS QTY_1
                  ,'LBS' AS UOM_1
                  ,H.DATETIME_DUMP
                  ,'' AS COMMENT_LINE
                  ,'N' AS PROCESSED
                  ,'N' AS REPROCESS
                  ,GETDATE() AS PROCESSED_DATE
                  ,'' AS HIST_OR_ERR
                  ,I.INGR_CODE
                  ,0 AS ID
                  ,H.BATCH_ID AS MTS_BATCH_ID
                  ,H.LINE AS MTS_LINE
                FROM SDWINENG01.ROSS_INTERFACE.dbo.BATCH_HEADER AS H
                INNER JOIN VW_JOBS
                ON  H.JOB_NUMBER = VW_JOBS.JOB_NUMBER
                    AND H.FACTORY = VW_JOBS.FACTORY
                LEFT OUTER JOIN VW_RECIPE_LINES AS RL
                RIGHT OUTER JOIN SDWINENG01.ROSS_INTERFACE.dbo.BATCH_INGR AS I
                ON  RL.PART_CODE = I.INGR_CODE
                ON  VW_JOBS.COMPANY_CODE = RL.COMPANY_CODE
                    AND VW_JOBS.FACTORY = RL.FACTORY
                    AND VW_JOBS.STD_PROCESS_SPEC = RL.RECIPE_CODE
                    AND H.BATCH_ID = I.BATCH_ID
                WHERE H.ROSS_PROCESSED = 'T'
                    AND H.DATETIME_DUMP BETWEEN '02-14-2011' AND '02-15-2011'
                    AND RL.PART_CODE IS NOT NULL) ;
                
--AFTER INSERT TO #RowsToProcess THEN INSERT TO IREN_PM
                INSERT
                INTO [fin_sim].[dbo].[IREN_PM]
                    ( [TRANSACTION_CODE]
                      ,[COMPANY_CODE]
                      ,[FACTORY]
                      ,[JOB_NUMBER]
                      ,[PROCESS_STAGE]
                      ,[RECIPE_LINE_NUMBER]
                      ,[WAREHOUSE]
                      ,[TAG_ID]
                      ,[BIN_LOCATION]
                      ,[QTY_1]
                      ,[UOM_1]
                      ,[TRANSACTION_DATE]
                      ,[COMMENT_LINE]
                      ,[PROCESSED]
                      ,[REPROCESS]
                      ,[PROCESSED_DATE]
                      ,[HIST_OR_ERR]
                      ,[PART_CODE]
                      ,[ID]
                      ,[MTS_BATCH_ID]
                      ,[MTS_LINE]
                    )
                    (SELECT * FROM #RowsToProcess) ;
                
/*
MARK ALL RECORDS A STATUS OF 'T' TO 'Y' TO INDICATE THEY WERE ALL INSERTED
INTO ROSS FOR PROCESSING.
*/
                
        UPDATE [SDWINENG01].[ROSS_INTERFACE].[dbo].BATCH_HEADER
        SET ROSS_PROCESSED = 'Y'
        WHERE ROSS_PROCESSED = 'T'
            AND DATETIME_DUMP BETWEEN '02-14-2011' AND '02-15-2011';
                
--AFTER INSERTED SUCCESSFULLY TO IREN_PM DROP #RowsToProcess
                
            DROP TABLE #RowsToProcess;
                
        COMMIT TRANSACTION;
            
    END TRY
            
--ROLLBACK THE TRANSACTIONS IF THERE WERE ANY ERRORS
            
    BEGIN CATCH
                
        ROLLBACK TRANSACTION;
        DECLARE @Msg NVARCHAR(MAX) ;
        SELECT @Msg = ERROR_MESSAGE() 
        RAISERROR('There was an error with this transaction: %s',20,101,@Msg) ;
            
    END CATCH
        
END

Open in new window

0
Comment
Question by:Hers2keep
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 

Author Comment

by:Hers2keep
ID: 35036944
I modified my catch statement based on this article (http://msdn.microsoft.com/en-us/library/ms179296.aspx) and now I get this error message:

Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "SDWINENG01". A nested transaction was required because the XACT_ABORT option was set to OFF.

Where in the order of my sproc would I put SET XACT_ABORT ON in order to have this sproc work?
0
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 35039021
The setting of SET XACT_ABORT is set at execute or run time and not at parse time so you must recreate the SP with that setting along with :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET SET XACT_ABORT ON
GO

CREATE proc [dbo].[dba_bla_bla]
as......

You could check under the linked server SSMS, ServerObjects->Linked Servers->Providers->right click SQLNCLI then properties, " and make sure allow inprocess is checked.

0
 

Author Comment

by:Hers2keep
ID: 35039047
It does have Allow inprocess checked. Let me try recreating the SP as you suggested and see what happens. When I set XACT_ABORT ON is that only for that one stored procedure?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 40

Expert Comment

by:lcohan
ID: 35039160
if that's the only one you need  yes. This is not a db or server setting you could turn on/off - please see more about that at:
http://msdn.microsoft.com/en-us/library/ms190707.aspx
0
 

Author Comment

by:Hers2keep
ID: 35039170
I changed it as you suggested and am still getting the same error.

Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "SDWINENG01". A nested transaction was required because the XACT_ABORT option was set to OFF.
0
 

Author Comment

by:Hers2keep
ID: 35137820
Ok I got this resolved. I had set XACT_ABORT ON in the wrong place. I moved it to immediately before the temp table was created and the SP ran exactly as it should. Thanks for all the help.
0
 

Author Closing Comment

by:Hers2keep
ID: 35137834
Only thing I had to do was move SET XACT_ABORT ON  to AFTER BEGIN TRANSACTION and that resolved the problem. Thanks for the help!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question