GP proc only executes 42 transactions - Dexterity call to a SQL Procedure contains cursor

Question

I am having issue with calling a SQL procedure from dexterity. The procedure contains cursor. This cursor is suppose to call another procedure which has a call to Dynamics GP Procedure 'taComputerChecklineinsert'. The working is supposed to be that the overall process has to insert transactions in the payroll transaction entry. Only a fixed number of 42 transactions get inserted. I have more than 42 transactions. If i execute the same procedure from SQL server with the same parameters itself it gives the required result. the issue comes up when i call from dexterity. what could be wrong?...i have been on this for long....and cannot figure out the issue.

Answer 1

Resolved finally. It has got nothing to go with any of the two econnect procedures namely 'taCreatePayrollBatchHeaderInsert' and 'taComputerChecklineinsert'.

It had raised due to a select statement before the batch creation by taCreatePayrollBatchHeaderInsert. the select statement was in place to select the parameters for taCreatePayrollBatchHeaderInsert.

The code worked perfectly fine when the select statement was commented.

CREATE PROC [dbo].[GTG_PR_Create_ABS_Trx]  

  @CMPANYID   INT  
, @UPRBCHOR   INT   -- 1 = Computer Check , 2 = Manual Check        
, @BACHNUMB   CHAR(15)        
, @EMPLOYID   CHAR(15)        
, @COMPTRTP   SMALLINT -- Computer transaction type:1 = Pay code; 2 = Deduction; 3 = Benefit        
, @SALCHG   SMALLINT -- Salary change ; required if passing a salary pay code:1 = Reallocate dollars; 2 = Reallocate hours;3=Reduce dollars;4=Reduce hours;=5=Additional amount        
, @UPRTRXCD   CHAR(6)  -- (OT , ABS)        
, @TRXBEGDT   DATETIME        
, @TRXENDDT   DATETIME        
, @Amount   NUMERIC(19 , 5) -- Amount        
, @ProcessStatus  INT  OUT            
, @ErrorState  INT   OUT      
, @ErrString   VARCHAR(255) OUT      

AS        


set @ErrorState = 0    
set @ErrString  = ''    

 -- Create batch if it doesn`t exist        
 IF NOT EXISTS( SELECT 1 FROM DYNAMICS..UPR10304 WHERE BACHNUMB = @BACHNUMB AND CMPANYID = @CMPANYID AND UPRBCHOR = @UPRBCHOR )        

BEGIN        

 **--SELECT  @BACHNUMB      
 --    ,@UPRBCHOR      
 --    ,@ErrorState    
 --    ,@ErrString**     


   EXEC  taCreatePayrollBatchHeaderInsert        
       @I_vBACHNUMB =  @BACHNUMB        
     , @I_vUPRBCHOR =  @UPRBCHOR        
     , @O_iErrorState  =  @ErrorState  OUT        
     , @oErrString  =  @ErrString  OUT     

  -- Associate employee deduction code if association doesn`t exist        
  IF NOT EXISTS(SELECT 1 FROM UPR00500 WHERE EMPLOYID = @EMPLOYID AND DEDUCTON = @UPRTRXCD)        
   BEGIN        
    EXEC taCreateEmployeeDeduction        
     @I_vEMPLOYID  =  @EMPLOYID         
   , @I_vDEDUCTON  =  @UPRTRXCD        
   , @O_iErrorState =  @ErrorState  OUT        
   , @oErrString  =  @ErrString  OUT                    


   END        

  -- Create Transaction        

  EXEC taCreateComputerCheckLineInsert        

            @I_vBACHNUMB    = @BACHNUMB        
           , @I_vEMPLOYID   = @EMPLOYID        
           , @I_vCOMPTRTP   = @COMPTRTP        
           , @I_vSALCHG     = @SALCHG         
           , @I_vUPRTRXCD   = @UPRTRXCD        
           , @I_vTRXBEGDT   = @TRXBEGDT        
           , @I_vTRXENDDT   = @TRXENDDT        
           , @I_vVARDBAMT   = @Amount  
           , @O_iErrorState = @ErrorState OUT        
           , @oErrString    = @ErrString  OUT                    

END        


GO