Solved

Stored Procedure doesn't process all the rows the first time it's run

Posted on 2012-04-09
6
453 Views
Last Modified: 2012-04-21
I have an application built using Delphi 5 which executes a used created SQL Server stored procedure after some internal processing and I have found that thestored procedure does not always process every row as it should.  The process goes as follows.

* Delphi application starts a transaction
* Dephi application inserts rows into several tables withput errors
* Delphi application commits the rows
* Delphi application uses a "TODBCStoredProc" component to execute the procedure and does not wait for a return value but does not appear to have an error or no rows would be processed

I then check the results of the stored procedure which is supposed to UPDATE the rows that have just been inserted and about 75% of the time some of the inserted rows typically the last ones inserted do not get updated by the stored procedure.  If I then run the stored procedure manually through SQL or if run the Delphi applicaition it runs the procedure without and errors and processes the left over rows without issues and may leave behind other unprocessed rows.  
My thinking is that I am not hitting errors in my stored procedure or the left over rows would never get processed the next time.

So.  I am trying to figure out what could possibly be happening that my stored procedure would not process all the rows.
Is it possible that the process is running before the commit really takes affect, even though I commit in the application prior?
Is there a way for me to find out through SQL Server why this happens?
Should I add more error handling of some sort and if so where?

Below is the stored procedure which is in question.

ALTER PROCEDURE [dbo].[LABOR_EXPORT]
AS
	-- declare variables for data values
	DECLARE  
	@res_fld1 varchar(20),
	@psl_key numeric(11, 0),
	@psl_prj numeric(11, 0),
	@prj_name varchar(30),
	@psl_chh numeric(11, 0),
	@chh_code varchar(100),
	@chh_fld1 varchar(20),
	@chh_fld2 varchar(20),
	@psl_fld1 varchar(20),
	@psl_fld2 varchar(20),
	@psd_key numeric(11, 0),
	@psd_date char(8),
	@psd_min numeric(11, 0),
	@prj_fld1 varchar(15),
	@emh_code varchar(20),
	@psl_rat_cd varchar(20),
	@dres_fld1 numeric(15,2);
	
		-- declare variables for export values
	DECLARE 
	@vldwk varchar(10),
	@vlphrw numeric(11, 2),
	@vlepa varchar(1),
	@vlmcuo varchar(30),
	@vlsub varchar(1),
	@vlsbl varchar(1),
	@vlsblt varchar(1),
	@vlopsq varchar(20),
	@vlshft varchar(1),
	@vlphrt varchar(1),
	@vljbcd varchar(20),
	@vljbst varchar(20),
	@vlwcnt char(2),
	@vlwcty char(2),
	@vlwst char(2),
	@vlp002 varchar(20),
	@vlall varchar(255),
	@res_fld1val varchar(20),
	@vlpdba varchar(10);
	
	-- declare variables for calculating Julian Date
	DECLARE 
	@century varchar(1),
	@year char(2),
	@doy int;
	
	-- fetch cursor for posted data to process
	DECLARE p_cursor CURSOR FOR 
		
		SELECT RES_FLD1, PSL_KEY, PSL_PRJ, PRJ_FLD1, PRJ_NAME, PSL_CHH, CHH_CODE, CHH_FLD1, PSL_FLD1, PSL_FLD2,PSD_KEY, PSD_DATE, PSD_MIN, EMH_CODE, PSL_RAT_CD, CHH_FLD2
		FROM PSHEADER
		JOIN TCRES ON RES_KEY=PSH_RES
		JOIN PSLINES ON PSL_PSH=PSH_KEY
		JOIN CHRHIS ON CHH_KEY=PSL_CHH
		JOIN TCPROJ ON PRJ_KEY=PSL_PRJ
		JOIN PSDETAIL ON PSD_PSL=PSL_KEY
		JOIN EMPHIS ON EMH_KEY=PSH_EMH
		WHERE (PSL_RAT_CD <> 'INCENTIVE' AND PSL_RAT_CD <> 'MOBILITY' AND (PSD_JDETYPE IS NULL OR PSD_JDETYPE=''))

	OPEN p_cursor FETCH NEXT FROM p_cursor INTO @res_fld1, @psl_key, @psl_prj,@prj_fld1, @prj_name, @psl_chh, @chh_code, @chh_fld1, @psl_fld1, @psl_fld2, @psd_key, @psd_date, @psd_min, @emh_code, @psl_rat_cd, @chh_fld2;

	-- iterate
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		SET @vldwk =  SUBSTRING(@psd_date,5,2) + '/' + RIGHT(@psd_date,2) + '/' + LEFT(@psd_date,4)
		SET @vlphrw = (@psd_min/60); -- labor hours
		SET @vlepa = '';		
		SET @vlmcuo = '';
		SET @vlsub = '';
		SET @vlsbl = '';
		SET @vlsblt = '';
		SET @vlopsq = @res_fld1;
		SET @vlshft = '';
		SET @vlphrt = '';
		SET @vljbcd = '';
		SET @vljbst = '';
		SET @vlwcnt = '00';
		SET @vlwcty = '00';
		SET @vlwst = '00';
		SET @vlp002 = @chh_fld1;
		SET @dres_fld1=0;
		SET @res_fld1val=''
		SET @vlpdba = ''
		
		IF (@res_fld1 is not null) or (@res_fld1<>'')
		BEGIN try
		  SET @dres_fld1 = CAST(@res_fld1 as NUMERIC(15,2)) / 100
		  SET @res_fld1val = CAST(@dres_fld1 AS VARCHAR(15))
		END try
		BEGIN CATCH
			SET @dres_fld1=0;
			SET @res_fld1val=''
		END CATCH
		
		SET @vlpdba = @psl_rat_cd
		
		IF @chh_fld2<>''
		BEGIN
			SET @vlpdba = @chh_fld2
		END
				
		IF (@chh_fld1 <= '12')
		BEGIN
			SET @vlmcuo = @prj_fld1;
		END
		
		IF (@chh_fld1 = '06' or @chh_fld1 = '09' or @chh_fld1 = '11')
		BEGIN
			SET @vljbcd = @psl_fld1;
		    IF @psl_fld2 is not null 
		    BEGIN
			    SET @vljbcd = @psl_fld2;
		    END
			--SET @vljbst = @chh_fld1;
		END

		-- update details...
		SET @vlall = @emh_code+','+@vldwk+','+@vlpdba+','+CAST(@vlphrw AS VARCHAR(15))+','+@vlepa+','+@vlmcuo+','+@vlsub+','+@vlsbl+','+@vlsblt+','+@res_fld1val+','+@vlshft+','+@vlphrt+','+@psl_fld1+','+@vljbst+','+@vlwcnt+','+@vlwcty+','+@vlwst+','+@chh_fld1+','

		
		UPDATE PSDETAIL SET 
			PSD_VLDWK=@vldwk, PSD_VLPHRW=@vlphrw, PSD_VLEPA=null, PSD_VLMCUO=@vlmcuo,
			PSD_VLSUB=null, PSD_VLSBL=@vlsbl, PSD_VLSBLT=@vlsblt, PSD_VLOPSQ=@vlopsq,
			PSD_VLSHFT=@vlshft, PSD_VLPHRT=@vlphrt, PSD_VLJBCD=@vljbcd, PSD_VLJBST=@vljbst,
			PSD_VLWCNT=@vlwcnt, PSD_VLWCTY=@vlwcty, PSD_VLWST=@vlwst, PSD_VLP002=@vlp002,PSD_JDETYPE='LABOR',
			PSD_JDELAB=@vlall
		WHERE PSD_KEY=@psd_key;
		
		-- log records as we process them
		INSERT INTO JDE_LOG 
		(
			JDE_EXPORT, RES_FLD1,
			PSL_KEY, PSL_PRJ, PSL_CHH, PSL_FLD1,
			PRJ_NAME, CHH_CODE, CHH_FLD1,
			PSD_KEY, PSD_DATE, PSD_MIN,
			JDE_VLDWK, JDE_VLPHRW, JDE_VLEPA, JDE_VLMCUO, JDE_VLSUB,
			JDE_VLSBL, JDE_VLSBLT, JDE_VLOPSQ, JDE_VLSHFT, JDE_VLPHRT, 
			JDE_VLJBCD, JDE_VLJBST, JDE_VLWCNT, JDE_VLWCTY, JDE_VLWST, JDE_VLP002,
			JDE_TSTMP
		)
		VALUES 
		(
			'LABOR', @res_fld1, 
			@psl_key, @psl_prj, @psl_chh, @vljbcd, 
			@prj_fld1, @chh_code, @chh_fld1, 
			@psd_key, @psd_date, @psd_min,
			@vldwk, @vlphrw, null, @vlmcuo, null, 
			@vlsbl, @vlsblt, @vlopsq, @vlshft, @vlphrt, 
			@vljbcd, @vljbst, @vlwcnt, @vlwcty, @vlwst, @vlp002,
			CONVERT(varchar, GETDATE(), 0)
		);
		
		-- get next record...	
		FETCH NEXT FROM p_cursor INTO @res_fld1, @psl_key, @psl_prj, @prj_fld1, @prj_name, @psl_chh, @chh_code, @chh_fld1, @psl_fld1, @psl_fld2,@psd_key, @psd_date, @psd_min, @emh_code, @psl_rat_cd, @chh_fld2;
	END
	CLOSE p_cursor 
	DEALLOCATE p_cursor

Open in new window

0
Comment
Question by:hmstechsupport
  • 3
  • 2
6 Comments
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 200 total points
ID: 37825699
Is it possible that the process is running before the commit really takes affect, even though I commit in the application prior?

Not as you've described the delphi sequence.



My thinking is that I am not hitting errors in my stored procedure or the left over rows would never get processed the next time.

Not necessarily, for example you could be erring transient condition like a lock timeout than has been released by next time around.


Should I add more error handling of some sort and if so where?

That's certainly the first thing I would do:  put the entire procedure in a try/catch block and report out any failures that are detected.  Standard operating procedure for an unattended background process.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37826036
to me, looks like you forget the issue commit...
try adding a commit after all your code is finished... maybe you have a loop and commit/start transaction
but when you are on the last loop, you forget to commit, which the updated rows are rolled back...
0
 

Author Comment

by:hmstechsupport
ID: 37828776
Ok. I made some changes and added additional logging as follows.
Select the number of rows to process (same select except with a count(*).  That returns 21 rows and is placed in the 2nd column here which is JDE_LABCNTSTRT.  The 3rd column JDE_LABCNTFIN SHOULD BE updated by the 3rd to last statement of the procedure but it is not which leaves me thinking there is an error, but I also enveloped the entire process in a try/catch but no row gets inserted by that,  I checked the cursor state using the following but it not open as far as I can see.
Any other suggestions, thoughts or improvements I can make to this procedure to find out what's going on?

IF CURSOR_STATUS('global','p_cursor')>=-1
BEGIN
 Print 'Still Open'
END

COLUMNS = JDE_TSTMP,  JDE_LABCNTSTRT, JDE_LABCNTFIN,  JDE_LABKEY
Apr 10 2012 11:56AM      21          NULL      2
Apr 10 2012 11:56AM      NULL      1      2
Apr 10 2012 11:56AM      NULL      2      2
Apr 10 2012 11:56AM      NULL      3      2
Apr 10 2012 11:56AM      NULL      4      2
Apr 10 2012 11:56AM      NULL      5      2
Apr 10 2012 11:56AM      NULL      6      2
Apr 10 2012 11:56AM      NULL      7      2
Apr 10 2012 11:56AM      NULL      8      2
Apr 10 2012 11:56AM      NULL      9      2
Apr 10 2012 11:56AM      NULL      10      2
Apr 10 2012 11:56AM      NULL      11      2
Apr 10 2012 11:56AM      NULL      12      2
Apr 10 2012 11:56AM      NULL      13      2


ALTER PROCEDURE [dbo].[LABOR_EXPORT]
AS
      -- declare variables for data values
      DECLARE  
      @res_fld1 varchar(20),
      @psl_key numeric(11, 0),
      @psl_prj numeric(11, 0),
      @prj_name varchar(30),
      @psl_chh numeric(11, 0),
      @chh_code varchar(100),
      @chh_fld1 varchar(20),
      @chh_fld2 varchar(20),
      @psl_fld1 varchar(20),
      @psl_fld2 varchar(20),
      @psd_key numeric(11, 0),
      @psd_date char(8),
      @psd_min numeric(11, 0),
      @prj_fld1 varchar(15),
      @emh_code varchar(20),
      @psl_rat_cd varchar(20),
      @dres_fld1 numeric(15,2);
      
            -- declare variables for export values
      DECLARE
      @vldwk varchar(10),
      @vlphrw numeric(11, 2),
      @vlepa varchar(1),
      @vlmcuo varchar(30),
      @vlsub varchar(1),
      @vlsbl varchar(1),
      @vlsblt varchar(1),
      @vlopsq varchar(20),
      @vlshft varchar(1),
      @vlphrt varchar(1),
      @vljbcd varchar(20),
      @vljbst varchar(20),
      @vlwcnt char(2),
      @vlwcty char(2),
      @vlwst char(2),
      @vlp002 varchar(20),
      @vlall varchar(255),
      @res_fld1val varchar(20),
      @vlpdba varchar(10);
      
      -- declare variables for calculating Julian Date
      DECLARE
      @century varchar(1),
      @year char(2),
      @doy int;
      
      -- declare log variables
      DECLARE @REC_COUNT numeric(11)
      DECLARE @PROC_COUNT numeric(11)
      DECLARE @JDE_LABKEY numeric(11)
      
      SET @PROC_COUNT = 0
      SET @REC_COUNT = 0
      SET @JDE_LABKEY = 0
      
      SELECT @JDE_LABKEY=MAX(JDE_LABKEY) FROM JDE_LOG
      IF @JDE_LABKEY < 1 OR @JDE_LABKEY IS NULL
      BEGIN
            SET @JDE_LABKEY = 1
      END
      ELSE
      BEGIN
            SET @JDE_LABKEY = @JDE_LABKEY + 1
      END
      
      SELECT @REC_COUNT = COUNT(*)
            FROM PSHEADER
            JOIN TCRES ON RES_KEY=PSH_RES
            JOIN PSLINES ON PSL_PSH=PSH_KEY
            JOIN CHRHIS ON CHH_KEY=PSL_CHH
            JOIN TCPROJ ON PRJ_KEY=PSL_PRJ
            JOIN PSDETAIL ON PSD_PSL=PSL_KEY
            JOIN EMPHIS ON EMH_KEY=PSH_EMH
            WHERE (PSL_RAT_CD <> 'INCENTIVE' AND PSL_RAT_CD <> 'MOBILITY' AND (PSD_JDETYPE IS NULL OR PSD_JDETYPE=''))

            INSERT INTO JDE_LOG (JDE_LABKEY,JDE_LABCNTSTRT,JDE_TSTMP) VALUES (@JDE_LABKEY,@REC_COUNT,CONVERT(varchar, GETDATE(), 0))


      -- fetch cursor for posted data to process
      DECLARE p_cursor CURSOR FOR
            
            SELECT RES_FLD1, PSL_KEY, PSL_PRJ, PRJ_FLD1, PRJ_NAME, PSL_CHH, CHH_CODE, CHH_FLD1, PSL_FLD1, PSL_FLD2,PSD_KEY, PSD_DATE, PSD_MIN, EMH_CODE, PSL_RAT_CD, CHH_FLD2
            FROM PSHEADER
            JOIN TCRES ON RES_KEY=PSH_RES
            JOIN PSLINES ON PSL_PSH=PSH_KEY
            JOIN CHRHIS ON CHH_KEY=PSL_CHH
            JOIN TCPROJ ON PRJ_KEY=PSL_PRJ
            JOIN PSDETAIL ON PSD_PSL=PSL_KEY
            JOIN EMPHIS ON EMH_KEY=PSH_EMH
            WHERE (PSL_RAT_CD <> 'INCENTIVE' AND PSL_RAT_CD <> 'MOBILITY' AND (PSD_JDETYPE IS NULL OR PSD_JDETYPE=''))

      OPEN p_cursor FETCH NEXT FROM p_cursor INTO @res_fld1, @psl_key, @psl_prj,@prj_fld1, @prj_name, @psl_chh, @chh_code, @chh_fld1, @psl_fld1, @psl_fld2, @psd_key, @psd_date, @psd_min, @emh_code, @psl_rat_cd, @chh_fld2;

      -- iterate
      WHILE @@FETCH_STATUS = 0
      BEGIN
      BEGIN TRY
          SET @PROC_COUNT = @PROC_COUNT + 1
            -- calculate julian date
            IF (LEFT(@psd_date, 2) = '20')
            BEGIN
                  SET @century = '1';
            END
            ELSE
            BEGIN
                  SET @century = '';
            END
            SET @year = SUBSTRING(@psd_date, 3, 2);
            SET @doy = DATEPART(DAYOFYEAR, CONVERT(DateTime, @psd_date, 112));
            
            -- populate export values
            SET @vldwk = convert(varchar,@doy) + @year + @century; -- julian date
            SET @vldwk =  SUBSTRING(@psd_date,5,2) + '/' + RIGHT(@psd_date,2) + '/' + LEFT(@psd_date,4)
            SET @vlphrw = (@psd_min/60); -- labor hours
            SET @vlepa = '';            
            SET @vlmcuo = '';
            SET @vlsub = '';
            SET @vlsbl = '';
            SET @vlsblt = '';
            SET @vlopsq = @res_fld1;
            SET @vlshft = '';
            SET @vlphrt = '';
            SET @vljbcd = '';
            SET @vljbst = '';
            SET @vlwcnt = '00';
            SET @vlwcty = '00';
            SET @vlwst = '00';
            SET @vlp002 = @chh_fld1;
            SET @dres_fld1=0;
            SET @res_fld1val=''
            SET @vlpdba = ''
            
            IF (@res_fld1 is not null) or (@res_fld1<>'')
            BEGIN try
              SET @dres_fld1 = CAST(@res_fld1 as NUMERIC(15,2)) / 100
              SET @res_fld1val = CAST(@dres_fld1 AS VARCHAR(15))
            END try
            BEGIN CATCH
                  SET @dres_fld1=0;
                  SET @res_fld1val=''
            END CATCH
            
            SET @vlpdba = @psl_rat_cd
            
            IF @chh_fld2<>''
            BEGIN
                  SET @vlpdba = @chh_fld2
            END
                        
            IF (@chh_fld1 <= '12')
            BEGIN
                  SET @vlmcuo = @prj_fld1;
            END
            
            IF (@chh_fld1 = '06' or @chh_fld1 = '09' or @chh_fld1 = '11')
            BEGIN
                  SET @vljbcd = @psl_fld1;
                IF @psl_fld2 is not null
                BEGIN
                      SET @vljbcd = @psl_fld2;
                END
                  --SET @vljbst = @chh_fld1;
            END

            -- update details...
            SET @vlall = @emh_code+','+@vldwk+','+@vlpdba+','+CAST(@vlphrw AS VARCHAR(15))+','+@vlepa+','+@vlmcuo+','+@vlsub+','+@vlsbl+','+@vlsblt+','+@res_fld1val+','+@vlshft+','+@vlphrt+','+@psl_fld1+','+@vljbst+','+@vlwcnt+','+@vlwcty+','+@vlwst+','+@chh_fld1+','

            
            UPDATE PSDETAIL SET
                  PSD_VLDWK=@vldwk, PSD_VLPHRW=@vlphrw, PSD_VLEPA=null, PSD_VLMCUO=@vlmcuo,
                  PSD_VLSUB=null, PSD_VLSBL=@vlsbl, PSD_VLSBLT=@vlsblt, PSD_VLOPSQ=@vlopsq,
                  PSD_VLSHFT=@vlshft, PSD_VLPHRT=@vlphrt, PSD_VLJBCD=@vljbcd, PSD_VLJBST=@vljbst,
                  PSD_VLWCNT=@vlwcnt, PSD_VLWCTY=@vlwcty, PSD_VLWST=@vlwst, PSD_VLP002=@vlp002,PSD_JDETYPE='LABOR',
                  PSD_JDELAB=@vlall
            WHERE PSD_KEY=@psd_key;
            
            -- log records as we process them
            INSERT INTO JDE_LOG
            (
                  JDE_EXPORT, RES_FLD1,
                  PSL_KEY, PSL_PRJ, PSL_CHH, PSL_FLD1,
                  PRJ_NAME, CHH_CODE, CHH_FLD1,
                  PSD_KEY, PSD_DATE, PSD_MIN,
                  JDE_VLDWK, JDE_VLPHRW, JDE_VLEPA, JDE_VLMCUO, JDE_VLSUB,
                  JDE_VLSBL, JDE_VLSBLT, JDE_VLOPSQ, JDE_VLSHFT, JDE_VLPHRT,
                  JDE_VLJBCD, JDE_VLJBST, JDE_VLWCNT, JDE_VLWCTY, JDE_VLWST, JDE_VLP002,
                  JDE_TSTMP, JDE_LABCNTFIN, JDE_LABKEY
            )
            VALUES
            (
                  'LABOR', @res_fld1,
                  @psl_key, @psl_prj, @psl_chh, @vljbcd,
                  @prj_fld1, @chh_code, @chh_fld1,
                  @psd_key, @psd_date, @psd_min,
                  @vldwk, @vlphrw, null, @vlmcuo, null,
                  @vlsbl, @vlsblt, @vlopsq, @vlshft, @vlphrt,
                  @vljbcd, @vljbst, @vlwcnt, @vlwcty, @vlwst, @vlp002,
                  CONVERT(varchar, GETDATE(), 0), @PROC_COUNT, @JDE_LABKEY
            );

            -- get next record...      
            FETCH NEXT FROM p_cursor INTO @res_fld1, @psl_key, @psl_prj, @prj_fld1, @prj_name, @psl_chh, @chh_code, @chh_fld1, @psl_fld1, @psl_fld2,@psd_key, @psd_date, @psd_min, @emh_code, @psl_rat_cd, @chh_fld2;

      END TRY
      BEGIN CATCH
            INSERT INTO JDE_LOG (JDE_LABKEY,JDE_LABCNTSTRT,JDE_TSTMP) VALUES (@JDE_LABKEY+1,@PROC_COUNT,CONVERT(varchar, GETDATE(), 0))
      END CATCH      

      END
      UPDATE JDE_LOG SET JDE_LABCNTFIN = @PROC_COUNT WHERE JDE_LABKEY=@JDE_LABKEY AND JDE_LABCNTFIN IS NULL
      CLOSE p_cursor
      DEALLOCATE p_cursor
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 42

Expert Comment

by:dqmq
ID: 37829464
Some observations:

1.  There's a problem with your logic.  If you do jump into the CATCH BLOCK and you do insert into the log, then you cycle back to the top of the WHILE loop and process the same record again, and again, and again.   You need to move the iterative FETCH after the CATCH block!!

FETCH FIRST
WHILE
BEGIN
  BEGIN TRY
  END TRY
  BEGIN CATCH
  END CATCH
  FETCH NEXT
END

2.  What if it's the LOG insert that's failing?  Then your CATCH BLOCK fails too!.  


3.    I think I would do it more like this:

ALTER PROCEDURE [dbo].[LABOR_EXPORT]
AS
      -- declare variables for data values
      DECLARE  
      @res_fld1 varchar(20),
      @psl_key numeric(11, 0),
      @psl_prj numeric(11, 0),
      @prj_name varchar(30),
      @psl_chh numeric(11, 0),
      @chh_code varchar(100),
      @chh_fld1 varchar(20),
      @chh_fld2 varchar(20),
      @psl_fld1 varchar(20),
      @psl_fld2 varchar(20),
      @psd_key numeric(11, 0),
      @psd_date char(8),
      @psd_min numeric(11, 0),
      @prj_fld1 varchar(15),
      @emh_code varchar(20),
      @psl_rat_cd varchar(20),
      @dres_fld1 numeric(15,2);
   
BEGIN TRY  
            -- declare variables for export values
      DECLARE
      @vldwk varchar(10),
      @vlphrw numeric(11, 2),
      @vlepa varchar(1),
      @vlmcuo varchar(30),
      @vlsub varchar(1),
      @vlsbl varchar(1),
      @vlsblt varchar(1),
      @vlopsq varchar(20),
      @vlshft varchar(1),
      @vlphrt varchar(1),
      @vljbcd varchar(20),
      @vljbst varchar(20),
      @vlwcnt char(2),
      @vlwcty char(2),
      @vlwst char(2),
      @vlp002 varchar(20),
      @vlall varchar(255),
      @res_fld1val varchar(20),
      @vlpdba varchar(10);
     
      -- declare variables for calculating Julian Date
      DECLARE
      @century varchar(1),
      @year char(2),
      @doy int;
     
      -- declare log variables
      DECLARE @REC_COUNT numeric(11)
      DECLARE @PROC_COUNT numeric(11)
      DECLARE @JDE_LABKEY numeric(11)
     
      SET @PROC_COUNT = 0
      SET @REC_COUNT = 0
      SET @JDE_LABKEY = 0
     
      SELECT @JDE_LABKEY=MAX(JDE_LABKEY) FROM JDE_LOG
      IF @JDE_LABKEY < 1 OR @JDE_LABKEY IS NULL
      BEGIN
            SET @JDE_LABKEY = 1
      END
      ELSE
      BEGIN
            SET @JDE_LABKEY = @JDE_LABKEY + 1
      END
     
      SELECT @REC_COUNT = COUNT(*)
            FROM PSHEADER
            JOIN TCRES ON RES_KEY=PSH_RES
            JOIN PSLINES ON PSL_PSH=PSH_KEY
            JOIN CHRHIS ON CHH_KEY=PSL_CHH
            JOIN TCPROJ ON PRJ_KEY=PSL_PRJ
            JOIN PSDETAIL ON PSD_PSL=PSL_KEY
            JOIN EMPHIS ON EMH_KEY=PSH_EMH
            WHERE (PSL_RAT_CD <> 'INCENTIVE' AND PSL_RAT_CD <> 'MOBILITY' AND (PSD_JDETYPE IS NULL OR PSD_JDETYPE=''))

            INSERT INTO JDE_LOG (JDE_LABKEY,JDE_LABCNTSTRT,JDE_TSTMP) VALUES (@JDE_LABKEY,@REC_COUNT,CONVERT(varchar, GETDATE(), 0))


      -- fetch cursor for posted data to process
      DECLARE p_cursor CURSOR FOR
           
            SELECT RES_FLD1, PSL_KEY, PSL_PRJ, PRJ_FLD1, PRJ_NAME, PSL_CHH, CHH_CODE, CHH_FLD1, PSL_FLD1, PSL_FLD2,PSD_KEY, PSD_DATE, PSD_MIN, EMH_CODE, PSL_RAT_CD, CHH_FLD2
            FROM PSHEADER
            JOIN TCRES ON RES_KEY=PSH_RES
            JOIN PSLINES ON PSL_PSH=PSH_KEY
            JOIN CHRHIS ON CHH_KEY=PSL_CHH
            JOIN TCPROJ ON PRJ_KEY=PSL_PRJ
            JOIN PSDETAIL ON PSD_PSL=PSL_KEY
            JOIN EMPHIS ON EMH_KEY=PSH_EMH
            WHERE (PSL_RAT_CD <> 'INCENTIVE' AND PSL_RAT_CD <> 'MOBILITY' AND (PSD_JDETYPE IS NULL OR PSD_JDETYPE=''))

      OPEN p_cursor FETCH NEXT FROM p_cursor INTO @res_fld1, @psl_key, @psl_prj,@prj_fld1, @prj_name, @psl_chh, @chh_code, @chh_fld1, @psl_fld1, @psl_fld2, @psd_key, @psd_date, @psd_min, @emh_code, @psl_rat_cd, @chh_fld2;

      -- iterate
      WHILE @@FETCH_STATUS = 0
      BEGIN
      BEGIN TRY
          SET @PROC_COUNT = @PROC_COUNT + 1
            -- calculate julian date
            IF (LEFT(@psd_date, 2) = '20')
            BEGIN
                  SET @century = '1';
            END
            ELSE
            BEGIN
                  SET @century = '';
            END
            SET @year = SUBSTRING(@psd_date, 3, 2);
            SET @doy = DATEPART(DAYOFYEAR, CONVERT(DateTime, @psd_date, 112));
           
            -- populate export values
            SET @vldwk = convert(varchar,@doy) + @year + @century; -- julian date
            SET @vldwk =  SUBSTRING(@psd_date,5,2) + '/' + RIGHT(@psd_date,2) + '/' + LEFT(@psd_date,4)
            SET @vlphrw = (@psd_min/60); -- labor hours
            SET @vlepa = '';            
            SET @vlmcuo = '';
            SET @vlsub = '';
            SET @vlsbl = '';
            SET @vlsblt = '';
            SET @vlopsq = @res_fld1;
            SET @vlshft = '';
            SET @vlphrt = '';
            SET @vljbcd = '';
            SET @vljbst = '';
            SET @vlwcnt = '00';
            SET @vlwcty = '00';
            SET @vlwst = '00';
            SET @vlp002 = @chh_fld1;
            SET @dres_fld1=0;
            SET @res_fld1val=''
            SET @vlpdba = ''
           
            IF (@res_fld1 is not null) or (@res_fld1<>'')
            BEGIN try
              SET @dres_fld1 = CAST(@res_fld1 as NUMERIC(15,2)) / 100
              SET @res_fld1val = CAST(@dres_fld1 AS VARCHAR(15))
            END try
            BEGIN CATCH
                  SET @dres_fld1=0;
                  SET @res_fld1val=''
            END CATCH
           
            SET @vlpdba = @psl_rat_cd
           
            IF @chh_fld2<>''
            BEGIN
                  SET @vlpdba = @chh_fld2
            END
                       
            IF (@chh_fld1 <= '12')
            BEGIN
                  SET @vlmcuo = @prj_fld1;
            END
           
            IF (@chh_fld1 = '06' or @chh_fld1 = '09' or @chh_fld1 = '11')
            BEGIN
                  SET @vljbcd = @psl_fld1;
                IF @psl_fld2 is not null
                BEGIN
                      SET @vljbcd = @psl_fld2;
                END
                  --SET @vljbst = @chh_fld1;
            END

            -- update details...
            SET @vlall = @emh_code+','+@vldwk+','+@vlpdba+','+CAST(@vlphrw AS VARCHAR(15))+','+@vlepa+','+@vlmcuo+','+@vlsub+','+@vlsbl+','+@vlsblt+','+@res_fld1val+','+@vlshft+','+@vlphrt+','+@psl_fld1+','+@vljbst+','+@vlwcnt+','+@vlwcty+','+@vlwst+','+@chh_fld1+','

           
            UPDATE PSDETAIL SET
                  PSD_VLDWK=@vldwk, PSD_VLPHRW=@vlphrw, PSD_VLEPA=null, PSD_VLMCUO=@vlmcuo,
                  PSD_VLSUB=null, PSD_VLSBL=@vlsbl, PSD_VLSBLT=@vlsblt, PSD_VLOPSQ=@vlopsq,
                  PSD_VLSHFT=@vlshft, PSD_VLPHRT=@vlphrt, PSD_VLJBCD=@vljbcd, PSD_VLJBST=@vljbst,
                  PSD_VLWCNT=@vlwcnt, PSD_VLWCTY=@vlwcty, PSD_VLWST=@vlwst, PSD_VLP002=@vlp002,PSD_JDETYPE='LABOR',
                  PSD_JDELAB=@vlall
            WHERE PSD_KEY=@psd_key;
           
            -- log records as we process them
            INSERT INTO JDE_LOG
            (
                  JDE_EXPORT, RES_FLD1,
                  PSL_KEY, PSL_PRJ, PSL_CHH, PSL_FLD1,
                  PRJ_NAME, CHH_CODE, CHH_FLD1,
                  PSD_KEY, PSD_DATE, PSD_MIN,
                  JDE_VLDWK, JDE_VLPHRW, JDE_VLEPA, JDE_VLMCUO, JDE_VLSUB,
                  JDE_VLSBL, JDE_VLSBLT, JDE_VLOPSQ, JDE_VLSHFT, JDE_VLPHRT,
                  JDE_VLJBCD, JDE_VLJBST, JDE_VLWCNT, JDE_VLWCTY, JDE_VLWST, JDE_VLP002,
                  JDE_TSTMP, JDE_LABCNTFIN, JDE_LABKEY
            )
            VALUES
            (
                  'LABOR', @res_fld1,
                  @psl_key, @psl_prj, @psl_chh, @vljbcd,
                  @prj_fld1, @chh_code, @chh_fld1,
                  @psd_key, @psd_date, @psd_min,
                  @vldwk, @vlphrw, null, @vlmcuo, null,
                  @vlsbl, @vlsblt, @vlopsq, @vlshft, @vlphrt,
                  @vljbcd, @vljbst, @vlwcnt, @vlwcty, @vlwst, @vlp002,
                  CONVERT(varchar, GETDATE(), 0), @PROC_COUNT, @JDE_LABKEY
            );

         
      END TRY
      BEGIN CATCH
            INSERT INTO JDE_LOG (JDE_LABKEY,JDE_LABCNTSTRT,JDE_TSTMP) VALUES (@JDE_LABKEY+1,@PROC_COUNT,CONVERT(varchar, GETDATE(), 0))
      END CATCH      

  -- get next record...      
            FETCH NEXT FROM p_cursor INTO @res_fld1, @psl_key, @psl_prj, @prj_fld1, @prj_name, @psl_chh, @chh_code, @chh_fld1, @psl_fld1, @psl_fld2,@psd_key, @psd_date, @psd_min, @emh_code, @psl_rat_cd, @chh_fld2;

      END
      UPDATE JDE_LOG SET JDE_LABCNTFIN = @PROC_COUNT WHERE JDE_LABKEY=@JDE_LABKEY AND JDE_LABCNTFIN IS NULL
      CLOSE p_cursor
      DEALLOCATE p_cursor
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION  
--issue some kind of alert, send an email, log to a text file,etc.--just avoid logging to a database table

END CATCH
0
 

Accepted Solution

by:
hmstechsupport earned 0 total points
ID: 37851140
I have rewritten the procedure into my Delphi code as I could not find out why the stored procedure would never process all the records.  Even the BEGIN TRY / CATCH did not help me.  
Thanks for your ideas, but nothing really solved the issue.
0
 

Author Closing Comment

by:hmstechsupport
ID: 37875023
I have rewritten the procedure into my Delphi code as I could not find out why the stored procedure would never process all the records.  Even the BEGIN TRY / CATCH did not help me.  
Thanks for your ideas, but nothing really solved the issue.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now