Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2005 LInked Server Problem

Posted on 2011-03-04
7
Medium Priority
?
456 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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 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