Solved

SQL Server 2005 LInked Server Problem

Posted on 2011-03-04
7
448 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
  • 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 39

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 39

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now