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.

I have tried to exclude the econnect procedure call to taComputerChecklineinsert from the cursor,the cursor works perfectly fine for the remaining workings, as it inserts the required number of transactions into the table. that concludes there is no issue with the call to econnect proc from the cursor. it has to do something with the dexterity call to 'taComputerChecklineinsert' — msgpdev10, Dec 07 '14 at 07:52
the issue is with the batch creation if created using the 'taCreatePayrollBatchHeaderInsert' This proc is restricting the number of transactions. Currently i tried by manually creating the batch header. then if this batch header is specified in the call to taComputerChecklineinsert, all the required transactions get inserted. — msgpdev10, Dec 07 '14 at 12:39

score 1 · Answer 1 · answered Dec 08 '14 at 08:04

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

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

1 Answers1