May 25, 2008 at 5:02 am
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
May 27, 2008 at 5:54 am
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.
May 27, 2008 at 6:01 am
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
July 21, 2008 at 4:26 am
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