Solved

SQL Server 2005 LInked Server Problem

Posted on 2011-03-04
7
449 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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