A severe error occured on the current command

  • I have this stored proc that is running between two linked servers. I have verified that MSDTC is working correctly using Microsofts MSDTC tester tool.

    The code is as follows and is being executed from serverA;

    ALTER PROCEDURE [dbo].[spImportSyncData]

    (

    @BatchSize BIGINT = 0

    )

    AS

    BEGIN

    --SET REMOTE_PROC_TRANSACTIONS OFF

    SET NOCOUNT ON;

    SET XACT_ABORT ON;

    PRINT '[spImportSyncData] Started';

    DECLARE @Rowcount BIGINT;

    DELETE [dbo].tblSyncData

    WHERE ProcessedDate IS NOT NULL;

    DELETE [ServerB].[DB2].[dbo].tblKPPSyncData

    WHERE KPPExportDate IS NOT NULL;

    SET @Rowcount = @@ROWCOUNT;

    PRINT '[spImportSyncData] Old records deleted count = ' + CAST(@Rowcount AS NVARCHAR(8));

    DECLARE @ReturnValue INT;

    SET @ReturnValue = 0;

    DECLARE @NOW DATETIME;

    SET @NOW = GETDATE();

    DECLARE @RecordsToImport TABLE

    (

    KPPSyncId BIGINT

    )

    DECLARE @FirstSyncRecord BIGINT;

    DECLARE @MaxRecords BIGINT;

    SELECT @MaxRecords = COUNT(*), @FirstSyncRecord = MIN(KPPSyncId)

    FROM

    [ServerB].[DB2].[dbo].tblKPPSyncData

    --[dbo].tblKPPSyncDatalocal

    WHERE KPPExportDate IS NULL;

    IF @BatchSize = 0 OR @BatchSize IS NULL

    BEGIN

    SET @BatchSize = @MaxRecords

    END

    BEGIN TRY

    BEGIN TRAN;

    PRINT '[spImportSyncData] Batch size = ' + CAST(@BatchSize AS NVARCHAR(8));

    PRINT '[spImportSyncData] Records awaiting import = ' + CAST(@MaxRecords AS NVARCHAR(8));

    PRINT '[spImportSyncData] First record id = ' + ISNULL(CAST(@FirstSyncRecord AS NVARCHAR(8)), ' ');

    INSERT @RecordsToImport

    SELECT KPPSyncId

    FROM [ServerB].[DB2].[dbo].tblKPPSyncData

    WHERE KPPExportDate IS NULL AND KPPSyncId < @FirstSyncRecord + @BatchSize;

    SET @Rowcount = @@Rowcount;

    PRINT '[spImportSyncData] Actual batch size = ' + CAST(@Rowcount AS NVARCHAR(8));

    INSERT tblSyncData (SyncNavisionId, SyncXml)

    SELECT NSD.KPPSyncId, NSD.KPPSyncXml

    FROM [ServerB].[DB2].[dbo].tblKPPSyncData NSD

    INNER JOIN @RecordsToImport RTI ON NSD.KPPSyncId = RTI.KPPSyncId;

    SET @Rowcount = @@Rowcount;

    PRINT '[spImportSyncData] Records imported count = ' + CAST(@Rowcount AS NVARCHAR(8));

    UPDATE [ServerB].[DB2].[dbo].tblKPPSyncData

    SET [KPPExportDate] = @NOW

    FROM @RecordsToImport RTI INNER JOIN [ServerB].[DB2].[dbo].tblKPPSyncData NSD

    ON RTI.KPPSyncId = NSD.KPPSyncId;

    SET @Rowcount = @@Rowcount;

    PRINT '[spImportSyncData] Records marked as exported count = ' + CAST(@Rowcount AS NVARCHAR(8));

    COMMIT TRAN;

    END TRY

    BEGIN CATCH

    PRINT '[spImportSyncData] ERROR: ' + ERROR_MESSAGE() + ' (' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) + ')';

    ROLLBACK TRAN;

    SET @ReturnValue = 1;

    END CATCH

    PRINT '[spImportSyncData] Exited';

    RETURN @ReturnValue;

    END

    The error I recieve is

    OLE DB provider "SQLNCLI" for linked server "serverB" returned message "No transaction is active.".

    Msg 0, Level 11, State 0, Line 0

    A severe error occurred on the current command. The results, if any, should be discarded.

    Server A is Win Server 2003 x64 SP1, SQL 2005 Standard SP2 64 bit. Version 9.0.3239

    Server B is Win Server 2003 SP1, SQL 2005 Standard SP2. Version 9.0.3152.

    Any ideas?

    Thanks

  • Just for anyone else who has received a similar error, I have managed to get the query to run for now by taking out the BEGIN TRAN and COMMIT. Not a solution but a temporary fix.

  • Check the remote server. You may very well find an access violation/stack dump.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Well, dont know if this will help but your post reg turning off TRAN solved my problem. Was getting the same error and couldn't figure out what was causing it but realised that I had issued a RETURN without either committing or rolling back the transaction. Sorted once i gave a ROLLBACK before the return.

    I guess that's not your problem really, but nevertheless thought I would post it!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply