Solved

SQL Server 2005 LInked Server Problem

Posted on 2011-03-04
7
454 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:Carla Romere
[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:Carla Romere
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:Carla Romere
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
Technology Partners: 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!

 
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:Carla Romere
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:Carla Romere
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:Carla Romere
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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