none
Loss Messages in WCF SQL Polling RRS feed

  • Question

  • BizTalk is polling records from SQL table through WCF custom adapter but all the records not picking from the table to BizTalk

    I am handling transactions and lock in the stored procedure .

    Select the rows from a table based on condition and update the status, but few records update to new status but not return to BizTalk                                                                                                                                                                                

    Records are updating from new to Inprogres which records are not returning to BizTalk, It’s happening temporarily not all the time

    I have disabled useAmbientTransaction in the adapter.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[spGetCaseId]
    AS
    BEGIN
    	SET NOCOUNT ON;
    	DECLARE @trancount INT;
    	SET @trancount = @@TRANCOUNT;
    	SET XACT_ABORT ON;
    	SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    	DECLARE @lockStatus INT
    
    	BEGIN TRY
    
    		EXEC @lockStatus = sp_getapplock @Resource='GetCase', @LockMode='Exclusive',@LockOwner='Session',@LockTimeout = 0
    		IF @lockStatus NOT IN (0, 1)
    		RETURN -1
    
    		IF @trancount = 0
    			BEGIN TRANSACTION
    		else
    			SAVE TRANSACTION spGetCaseId
    
     DECLARE @InMemoryPendingCaseNumbersTemp TABLE(CaseID INT, CaseCount INT)
    
     INSERT INTO @InMemoryPendingCaseNumbersTemp  SELECT [SalesforceCaseNumber],COUNT([SalesforceCaseNumber]) AS RecordCount 
     FROM [remitterpaymentitem_rdn] WITH (ROWLOCK READPAST)
    		WHERE  ProcessingStatus='New'
             GROUP BY [SalesforceCaseNumber] 
    		 ORDER BY [SalesforceCaseNumber] ASC
    
    SELECT DISTINCT Top(1) [SalesforceCaseNumber] 
      FROM tableA AS rmt WITH (ROWLOCK READPAST)  inner join @InMemoryPendingCaseNumbersTemp AS cn 
       ON rmt.BatchTotalRecordCount=cn.CaseCount and rmt.SalesforceCaseNumber=cn.CaseID
       	     
      UPDATE tableA WITH (ROWLOCK) SET ProcessingStatus='InProgress',
      UpdateTime=GETDATE(),LastUpdatedBy='XSBPoll'  WHERE SalesforceCaseNumber= (SELECT DISTINCT Top(1) [SalesforceCaseNumber] 
      FROM tableA AS rmt WITH (ROWLOCK READPAST)  inner join @InMemoryPendingCaseNumbersTemp AS cn 
       ON rmt.BatchTotalRecordCount=cn.CaseCount and rmt.SalesforceCaseNumber=cn.CaseID)
       
      IF @trancount = 0	  
     
     COMMIT TRANSACTION 	
     
     EXEC @lockStatus = sp_releaseapplock @Resource = 'GetCase', @LockOwner = 'Session'; 
    	
    END TRY
    	
    BEGIN CATCH
    		DECLARE @ERROR int, @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT, @XSTATE INT;
    
    		SELECT	@ERROR = ERROR_NUMBER(),
    				@ErrorMessage = ERROR_MESSAGE(),
    				@ErrorSeverity = ERROR_SEVERITY(),
    				@ErrorState = ERROR_STATE(),
    				@XSTATE = XACT_STATE();
    
    		--Release lock
    		DECLARE @lockState varchar(30)
    		SELECT @lockState = APPLOCK_MODE('public', 'GetCase', 'Session');	
    		IF @lockState <> 'NoLock'
    			EXEC @lockStatus = sp_releaseapplock  'GetCase', @LockOwner = 'Session';
    
    		IF @XSTATE = -1
    			ROLLBACK;
    		IF @XSTATE = 1 and @trancount = 0
    			ROLLBACK
    		IF @XSTATE = 1 and @trancount > 0
    			ROLLBACK TRANSACTION spGetCaseId;
    
    		RAISERROR (@ErrorMessage, 
    				   @ErrorSeverity,
    				   @ErrorState 
    				   );
    	END CATCH
    END
    


    Wednesday, September 18, 2019 3:20 AM

All replies