Link to home
Start Free TrialLog in
Avatar of apitech
apitech

asked on

ASP.Net web application on SQL 2000 box: Cannot insert the value NULL into column

Below is the error message that I get when running a 401k ASP.Net web application on a SQL 2000 (8.00.2039) server running .Net Framework 3.5.  The error message speaks to a field in our "MBSCUSTOM" database called "E401K".  Modifying this field to allow for NULLs is out of the question.

This application has been running just fine for years, literally.  Then, earlier this week, it stopped working and pulls the error message below.  Nothing has been installed on the server over this past week, so I don't get it.

The choices, before clicking the Retrieve button, are "All", "Monthly", "Bi-Weekly", and "Weekly".  What's funny is that the only time this error is retrieved is by clicking the "All" selection.  The others work fine.

I seriously doubt that this is a database issue.  I think that it is something related to the web app itself.

The app was originally designed to work in a .Net Framework 1.1 environment.  Since that was years ago, do you suppose that the app is outdated and needs to be replaced?

Anyway, here is the error.  Please let me know your thoughts:

Cannot insert the value NULL into column 'E401K', table 'MBSCUSTOM.dbo.Audit401KDetail'; column does not allow nulls. INSERT fails. Warning: The table '#HRINFO' has been created but its maximum row size (8354) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. Warning: The table '#HRINFO' has been created but its maximum row size (8351) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
INSERT MBSCUSTOM.dbo.Audit401KDetail 
	(
	  hid,PNUM, PRDATE, SSN, ytdHours, ytdPay, ytd415
	  ,[PT%], [AT%], PTDPay
	  ,PFC, CurrPTEE, CurrPTER
	  ,CurrAT, Loan
	  ,LoanPMT, [ER%], EMPName, Status
	  ,EN, HC, DOB, DOH, DOPE, DOT
	  ,ADOH, DIV, ADD1
	  ,ADD2, City, State, Zip, EmpType, CONV, SP
	  ,E401K
	)
	SELECT @runID, '001111'
	, CASE WHEN MCDATEPTEE IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATEPTEE AS datetime))
		WHEN MCDATEPTER IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATEPTER AS datetime))
		WHEN MCDATECURRLOAN IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATECURRLOAN AS datetime))
		ELSE dbo.ConvertDate(GetDate()) END
	,UP.SOCSCNUM, CASE WHEN YTDHOURS IS NOT NULL THEN RIGHT(dbo.Convert4V3Int(YTDHOURS),7) ELSE '0000000' END
	, CASE WHEN YTDWAGES IS NOT NULL THEN RIGHT(dbo.ConvertS9V2Dec3(YTDWAGES),11) ELSE '00000000000' END,'00000000000'
	, CASE WHEN #CurrPTEEAmounts.DEDNPRCT_1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTEEAmounts.DEDNPRCT_1),4) END, '0000'
	,CASE WHEN #CurrPayAmounts.PTD_Pay IS NULL THEN '00000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPayAmounts.PTD_Pay),11) END
	, CASE WHEN #CurrPTEEAmounts.PFC IS NOT NULL THEN CASE #CurrPTEEAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
		WHEN #CurrPTERAmounts.PFC IS NOT NULL THEN CASE #CurrPTERAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
		WHEN #CurrLoanAmounts.PFC IS NOT NULL THEN CASE #CurrLoanAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
		ELSE '0' END 
	, CASE WHEN #CurrPTEEAmounts.PTEE IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTEEAmounts.PTEE),9) END 
	, CASE WHEN #CurrPTERAmounts.PTER IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTERAmounts.PTER),9) END
	, '000000000', CASE WHEN #CurrLoanAmounts.LoanPMT > 0 THEN '001' ELSE '000' END
	, CASE WHEN #CurrLoanAmounts.LoanPMT IS NULL THEN '000000000' WHEN #CurrLoanAmounts.LoanPMT < 0 THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrLoanAmounts.LoanPMT),9) END
	, CASE WHEN #CurrPTERAmounts.BNFPRCNT_1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTERAmounts.BNFPRCNT_1),4) END , dbo.rightpad(HR.EMPNAME)
	, HR.HRSTATUS
	,'000000',HIGHLYCOMP, dbo.ConvertDate(CAST(UP.BRTHDATE AS datetime))
	, dbo.ConvertDate(CAST(UP.STRTDATE AS datetime)), DOPE
	/*
	, CASE WHEN HR.DOT <> '00000000' THEN dbo.ConvertDate(CAST(HR.DOT AS datetime)) ELSE HR.DOT  END*/
, CASE WHEN HR.DOT IS NOT NULL THEN dbo.ConvertDate(CAST(HR.DOT AS datetime)) ELSE '00000000'  END
	, ADOH,@DIV, dbo.rightpad(LTRIM(RTRIM(ADDRESS1)))
	, dbo.rightpad(LTRIM(RTRIM(ADDRESS2))), CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(CITY))),18) AS CHAR(18)) 
	, CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(STATE))),2) AS CHAR(2)), CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(ZIPCODE))),9) AS CHAR(9)) 
	, EMPCLASS,'00000000',SP
	, RIGHT(dbo.ConvertS9V2Dec3(HR.UPRTRXAM),9)
	FROM LII.dbo.UPR00100 UP
	/**  ALTER THE INNER JOINS
	INNER JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
	INNER JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
	INNER JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID    **/
	LEFT JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
	LEFT JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
	LEFT JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID
	LEFT JOIN #CurrPTEEAmounts ON UP.EMPLOYID = #CurrPTEEAmounts.EMPLOYID 
	LEFT JOIN #CurrPTERAmounts ON UP.EMPLOYID = #CurrPTERAmounts.EMPLOYID
	LEFT JOIN #CurrLoanAmounts ON UP.EMPLOYID = #CurrLoanAmounts.EMPLOYID
ORDER BY UP.EMPLOYID

Open in new window

Avatar of reb73
reb73
Flag of Ireland image

Think its a problem with the function dbo.ConvertS9V2Dec3 returning NULL values..

Try changing line 41 in your code above as follows -


The message "The table '#HRINFO' has been created but its maximum row size (8354) exceeds the maximum number of bytes per row (8060)" is just a warning as the defined size of the table exceeds the maximum allowed size of 8060 which is more or less a page (8K). This will result in an error only if the actual contents of a record exceed 8060 characters..
        ,COALESCE(RIGHT(dbo.ConvertS9V2Dec3(HR.UPRTRXAM),9), '')

Open in new window

Avatar of Anthony Perkins
>>Warning: The table '#HRINFO' has been created but... <<
This error message is not coming from the code you posted.  I suggest you post the CREATE TABLE #HRINFO ... and INSERT #HRINFO ... code, so that we can make recomendations.

>>I seriously doubt that this is a database issue.  I think that it is something related to the web app itself.<<
Actually it is neither one.  It is a user error: They are entering too much info :)
Avatar of apitech
apitech

ASKER

I'm afraid that reb73's suggestion did not work.  When I tried to apply the change to the stoed proc, I get that attached error.

On acperkins, I cannot get CREATE TABLE as this table is not created ny the code.  It is a temp table that SQL just creates.

Other ideas?
Gee-Whiz.docx
>> I cannot get CREATE TABLE as this table is not created ny the code.  It is a temp table that SQL just creates.<<
Then post the code where "SQL just creates" it.
Avatar of apitech

ASKER

Thanks, acperkins!

Below is the code!  I appreciate any help that you can offer.

CREATE PROCEDURE Produce401KLBI
(      @startDate as datetime,      
      @endDate as datetime,
      @frequency as integer,
      @runID as integer
      ,@DIV as CHAR(4)
      ,@rowCount as integer OUTPUT
)
AS
SET NOCOUNT ON
SET ANSI_PADDING ON
/***************
 Declarations
****************/
DECLARE @errNum INT
DECLARE @strStep AS VARCHAR(128)
DECLARE @strERR AS VARCHAR(1000)
DECLARE @currYear as CHAR(4)

/*******  JD 1/23/06
Changing date extraction code for testing
SET @currYear = DATEPART(yyyy,GetDate())
Also setting a first day parm
**********/
SET @currYear = DATEPART(yyyy,@startDate)

DECLARE @dayOne as char(10)
SELECT @dayOne = @currYear + '-01-01'

SELECT      @strStep = 'Create CurrPTEEAmounts'
-- Get deduction amounts from the check detail table, put in #CurrPTEEAmounts
SELECT      LBI.dbo.UPR30300.EMPLOYID, LEFT(LTRIM(RTRIM(MIN(LBI.dbo.UPR30300.PAYROLCD))),1) as PFC, MAX(LBI.dbo.UPR00500.DEDNPRCT_1)AS DEDNPRCT_1, MAX(CHEKDATE) AS MCDATEPTEE, Sum(CASE [PYRLRTYP] WHEN 2 THEN [UPRTRXAM] ELSE 0 END) AS PTEE
INTO      #CurrPTEEAmounts
FROM      (LBI.dbo.UPR00500 INNER JOIN KDedCodes ON LBI.dbo.UPR00500.DEDUCTON = KDedCodes.PAYROLCD)
            INNER JOIN LBI.dbo.UPR30300 ON (LBI.dbo.UPR00500.DEDUCTON = LBI.dbo.UPR30300.PAYROLCD) AND (LBI.dbo.UPR00500.EMPLOYID = LBI.dbo.UPR30300.EMPLOYID)
WHERE      LBI.dbo.UPR30300.CHEKDATE > @startDate AND LBI.dbo.UPR30300.CHEKDATE <= @endDate
            -- AND LBI.dbo.UPR00500.INACTIVE <> 1
GROUP BY LBI.dbo.UPR30300.EMPLOYID

SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler

--SELECT '===#CurrPTEEAmounts==='
--SELECT COUNT(EMPLOYID) FROM #CurrPTEEAmounts
--SELECT * FROM #CurrPTEEAmounts ORDER BY EMPLOYID

SELECT @strStep = 'Create CurrPTERAmounts'
-- Get bebefit/employer match amounts from the check detail table, put in #CurrPTERAmounts
SELECT      LBI.dbo.UPR30300.EMPLOYID,LEFT(LTRIM(RTRIM(LBI.dbo.UPR30300.PAYROLCD)),1) as PFC,LBI.dbo.UPR00600.BNFPRCNT_1, MAX(CHEKDATE) AS MCDATEPTER, Sum(CASE [PYRLRTYP] WHEN 3 THEN [UPRTRXAM] ELSE 0 END) AS PTER
INTO      #CurrPTERAmounts
FROM      LBI.dbo.UPR30300
            INNER JOIN (LBI.dbo.UPR00600 INNER JOIN KBenCodes ON LBI.dbo.UPR00600.BENEFIT = KBenCodes.PAYROLCD) ON (LBI.dbo.UPR30300.EMPLOYID = LBI.dbo.UPR00600.EMPLOYID)
                                                                                                                                                                    AND (LBI.dbo.UPR30300.PAYROLCD = LBI.dbo.UPR00600.BENEFIT)
WHERE      LBI.dbo.UPR30300.CHEKDATE > @startDate And LBI.dbo.UPR30300.CHEKDATE <= @endDate
-- AND LBI.dbo.UPR00600.INACTIVE<>1
GROUP BY LBI.dbo.UPR30300.EMPLOYID, LBI.dbo.UPR30300.PAYROLCD, LBI.dbo.UPR00600.BNFPRCNT_1

SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler

--SELECT '===#CurrPTERAmounts==='
--SELECT COUNT(EMPLOYID) FROM #CurrPTERAmounts
--SELECT * FROM #CurrPTERAmounts ORDER BY EMPLOYID

SELECT @strStep = 'Create CurrLoanAmounts'
-- Get loan amounts from the check detail table, put in #CurrLoanAmounts
SELECT      LBI.dbo.UPR30300.EMPLOYID, LEFT(LTRIM(RTRIM(LBI.dbo.UPR30300.PAYROLCD)),1) as PFC, MAX(CHEKDATE) AS MCDATECURRLOAN, Sum(CASE [PYRLRTYP] WHEN 2 THEN [UPRTRXAM] ELSE 0 END) AS LoanPMT
INTO      #CurrLoanAmounts
FROM      LBI.dbo.UPR30300
            INNER JOIN  KLoanCodes ON LBI.dbo.UPR30300.PAYROLCD =  KLoanCodes.PAYROLCD
WHERE      LBI.dbo.UPR30300.CHEKDATE > @startDate AND LBI.dbo.UPR30300.CHEKDATE <= @endDate
GROUP BY LBI.dbo.UPR30300.EMPLOYID, LBI.dbo.UPR30300.PAYROLCD

SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler

--SELECT '===#CurrLoanAmounts==='
--SELECT COUNT(EMPLOYID) FROM #CurrLoanAmounts
--SELECT COUNT(DISTINCT EMPLOYID ) FROM #CurrLoanAmounts
--SELECT * FROM #CurrLoanAmounts ORDER BY EMPLOYID


SELECT @strStep = 'Create CurrPayAmounts'
--  Get current paycheck amounts for employees, put in #CurrPayAmounts
--  This portion gets ALL employees in the check detail table
SELECT      LBI.dbo.UPR30300.EMPLOYID,/* LEFT(LTRIM(RTRIM(LBI.dbo.UPR30300.PAYROLCD)),1) as PFC,*/ MAX(CHEKDATE) AS MCDATECURRPAY, Sum(CASE [PYRLRTYP] WHEN 1 THEN [UPRTRXAM] ELSE 0 END) AS PTD_Pay
INTO      #CurrPayAmounts
FROM      LBI.dbo.UPR30300 INNER JOIN vw_LBIPayCodes ON LBI.dbo.UPR30300.PAYROLCD = vw_LBIPayCodes.BSDONCDE
WHERE      LBI.dbo.UPR30300.CHEKDATE > @startDate AND LBI.dbo.UPR30300.CHEKDATE <= @endDate
GROUP BY LBI.dbo.UPR30300.EMPLOYID--, LBI.dbo.UPR30300.PAYROLCD

SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler

--SELECT '===#CurrPayAmounts==='
--SELECT COUNT(*) FROM #CurrPayAmounts
--SELECT DISTINCT EMPLOYID FROM #CurrPayAmounts
--SELECT * FROM #CurrPayAmounts ORDER BY EMPLOYID

SELECT @strStep = 'Get Employee Totals'
--  Gets current totals.  If you run the procedure for a pay period that is before the current
--  pay period, then the totals will be wrong because they have been updated already with the
--  current pay period totals
SELECT      U3.EMPLOYID
,            SUM(MTDHOURS_1/100 + MTDHOURS_2/100 +MTDHOURS_3/100 +MTDHOURS_4/100 +MTDHOURS_5/100 +MTDHOURS_6/100 +MTDHOURS_7/100 +MTDHOURS_8/100 +MTDHOURS_9/100 +MTDHOURS_10/100 +MTDHOURS_11/100 + MTDHOURS_12/100) as YTDHOURS
,            SUM(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3 + MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6 + MTDWAGES_7 + MTDWAGES_8+ MTDWAGES_9 + MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12) AS YTDWAGES
INTO      #YTDInfo
FROM      LBI.dbo.UPR30301 U3 INNER JOIN LBI.dbo.UPR00100 U0 ON U3.EMPLOYID = U0.EMPLOYID
WHERE      U3.PYRLRTYP=1 and U3.YEAR1=@currYear
GROUP BY U3.EMPLOYID

--**OB** removed 'AND U0.INACTIVE=0' in where statement above

SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler

--SELECT '===#YTDInfo==='
--SELECT COUNT(EMPLOYID) FROM #YTDInfo
--SELECT COUNT(DISTINCT EMPLOYID) FROM #YTDInfo
--SELECT * FROM #YTDInfo ORDER BY EMPLOYID

SELECT @strStep = 'Get Employee Info'
--  Get employee info from the 100 and 102 tables, gets info for ALL employees

SELECT       U0.EMPLOYID
,            0 AS HIGHLYCOMP
,            U0.BRTHDATE
,            '00' as HRSTATUS
,            U0.STRTDATE
,            '00000000' AS DOPE
,            NULL AS DOT
,            '00000000' AS ADOH
,            U1.ADDRESS1
,            U1.ADDRESS2
,            U1.CITY
,            U1.State
,            REPLACE(U1.ZIPCODE,'-','') AS ZIPCODE
,            CASE RIGHT(LTRIM(RTRIM(EMPLCLAS)),1)
                  WHEN 'H' THEN 'B'
                  WHEN 'S' THEN 'C'
                  ELSE 'Z'
            END AS EMPCLASS
,            'N' AS SP
,            0.0 AS UPRTRXAM
,            LTRIM(RTRIM(U0.LastNAME))+ ', ' + LTRIM(RTRIM(U0.FRSTNAME)) + ' ' + LTRIM(RTRIM(U0.MIDLNAME)) AS EMPNAME
,            U0.INACTIVE
INTO      #HRINFO
FROM      LBI.dbo.UPR00100 U0
            INNER JOIN LBI.dbo.UPR00102 U1 ON U0.EMPLOYID = U1.EMPLOYID
WHERE      U0.EMPLOYID <> 'REPRINTED'

ALTER TABLE #HRINFO ALTER COLUMN DOT VARCHAR(10)
--UPDATE TERMINATED EMPLOYEES
UPDATE HR
      SET HR.HRSTATUS='30', HR.DOT=CAST(YEAR(TERMINATIONDATE_I) AS varchar(4)) + '-' + CAST (MONTH(TERMINATIONDATE_I) AS varchar(2))+ '-' +  CAST (DAY(TERMINATIONDATE_I) AS varchar(2))
FROM #HRINFO HR INNER JOIN LBI.dbo.TE024230 TER
      ON HR.EMPLOYID = TER.EMPID_I and HR.INACTIVE = 1

SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler

ALTER TABLE #HRINFO ALTER COLUMN UPRTRXAM NUMERIC(19,5)

SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler

UPDATE      HR
SET            UPRTRXAM=(CASE WHEN U3.TOTUPRTRXAM IS NOT NULL THEN U3.TOTUPRTRXAM ELSE 0.0 END)
FROM      #HRINFO HR
            LEFT JOIN (SELECT      EMPLOYID, SUM(UPRTRXAM) as TOTUPRTRXAM
                           FROM            LBI.dbo.UPR30300 U3
                           WHERE      pyrlrtyp=3 and payrolcd like '%07229' and CHEKDATE > @startDate
                           AND            CHEKDATE <= @endDate GROUP BY EMPLOYID) U3 ON HR.EMPLOYID = U3.EMPLOYID

SELECT @errNum = @@ERROR
IF @errNum <> 0 GOTO ErrorHandler

--SELECT '==HR INFO=='
--SELECT * FROM #HRINFO

--SELECT @strStep = 'Create Temp table to hold affected employee ids'
--SELECT EMPLOYID INTO #AFFECTEDEMPS
--FROM
--(select DISTINCT(EMPLOYID) FROM #CurrPTEEAmounts
--UNION
--select DISTINCT(EMPLOYID) FROM #CurrPTERAmounts
--UNION
--select DISTINCT(EMPLOYID) FROM #CurrLoanAmounts) as UEMPS

--SELECT '==#AFFECTEDEMPS=='
--SELECT * FROM #AFFECTEDEMPS


SELECT @strStep = 'Create 401KAggregate and audit trails'
--MOved to Extract401K
--INSERT MBSCUSTOM.dbo.Audit401KHeader (runDate, startDate, endDate, frequency,numRows)
--SELECT GetDate(),@startDate,@endDate,@frequency,0

--SET @runID = @@IDENTITY

IF @frequency = 0
BEGIN

      INSERT MBSCUSTOM.dbo.Audit401KDetail
      (
        hid,PNUM, PRDATE, SSN, ytdHours, ytdPay, ytd415
        ,[PT%], [AT%], PTDPay
        ,PFC, CurrPTEE, CurrPTER
        ,CurrAT, Loan
        ,LoanPMT, [ER%], EMPName, Status
        ,EN, HC, DOB, DOH, DOPE, DOT
        ,ADOH, DIV, ADD1
        ,ADD2, City, State, Zip, EmpType, CONV, SP
        ,E401K
      )
      SELECT      @runID
      ,            '001111'
      ,            CASE WHEN MCDATEPTEE IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATEPTEE AS datetime))
                        WHEN MCDATEPTER IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATEPTER AS datetime))
                        WHEN MCDATECURRLOAN IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATECURRLOAN AS datetime))
                        ELSE dbo.ConvertDate(GetDate())
                  END
      ,            UP.SOCSCNUM, CASE WHEN YTDHOURS IS NOT NULL THEN RIGHT(dbo.Convert4V3Int(YTDHOURS),7) ELSE '0000000' END
      ,            CASE WHEN YTDWAGES IS NOT NULL THEN RIGHT(dbo.ConvertS9V2Dec3(YTDWAGES),11) ELSE '00000000000' END,'00000000000'
      ,            CASE WHEN #CurrPTEEAmounts.DEDNPRCT_1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTEEAmounts.DEDNPRCT_1),4) END, '0000'
      ,            CASE WHEN #CurrPayAmounts.PTD_Pay IS NULL THEN '00000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPayAmounts.PTD_Pay),11) END
      ,            CASE WHEN #CurrPTEEAmounts.PFC IS NOT NULL THEN CASE #CurrPTEEAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
                        WHEN #CurrPTERAmounts.PFC IS NOT NULL THEN CASE #CurrPTERAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
                        WHEN #CurrLoanAmounts.PFC IS NOT NULL THEN CASE #CurrLoanAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
                        ELSE '0'
                  END
      ,            CASE WHEN #CurrPTEEAmounts.PTEE IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTEEAmounts.PTEE),9) END
      ,            CASE WHEN #CurrPTERAmounts.PTER IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTERAmounts.PTER),9) END
      ,            '000000000'
      ,            CASE WHEN #CurrLoanAmounts.LoanPMT > 0 THEN '001' ELSE '000' END
      ,            CASE WHEN #CurrLoanAmounts.LoanPMT IS NULL THEN '000000000' WHEN #CurrLoanAmounts.LoanPMT < 0 THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrLoanAmounts.LoanPMT),9) END
      ,            CASE WHEN #CurrPTERAmounts.BNFPRCNT_1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTERAmounts.BNFPRCNT_1),4) END , dbo.rightpad(HR.EMPNAME)
      ,            HR.HRSTATUS
      ,            '000000'
      ,            HIGHLYCOMP
      ,            dbo.ConvertDate(CAST(UP.BRTHDATE AS datetime))
      ,            dbo.ConvertDate(CAST(UP.STRTDATE AS datetime)), DOPE
      /*,            CASE WHEN HR.DOT <> '00000000' THEN dbo.ConvertDate(CAST(HR.DOT AS datetime)) ELSE HR.DOT  END*/
      ,            CASE WHEN HR.DOT IS NOT NULL THEN dbo.ConvertDate(CAST(HR.DOT AS datetime)) ELSE '00000000'  END
      ,            ADOH,@DIV, dbo.rightpad(LTRIM(RTRIM(ADDRESS1)))
      ,            dbo.rightpad(LTRIM(RTRIM(ADDRESS2))), CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(CITY))),18) AS CHAR(18))
      ,            CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(STATE))),2) AS CHAR(2)), CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(ZIPCODE))),9) AS CHAR(9))
      ,             EMPCLASS,'00000000'
      ,            SP
      ,            RIGHT(dbo.ConvertS9V2Dec3(HR.UPRTRXAM),9)
      FROM      LBI.dbo.UPR00100 UP
/**      ALTER THE INNER JOINS
                        INNER JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
                        INNER JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
                        INNER JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID    **/
                        LEFT JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
                        LEFT JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
                        LEFT JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID
                        LEFT JOIN #CurrPTEEAmounts ON UP.EMPLOYID = #CurrPTEEAmounts.EMPLOYID
                        LEFT JOIN #CurrPTERAmounts ON UP.EMPLOYID = #CurrPTERAmounts.EMPLOYID
                        LEFT JOIN #CurrLoanAmounts ON UP.EMPLOYID = #CurrLoanAmounts.EMPLOYID
      WHERE      UP.EMPLOYID <> 'REPRINTED'
      ORDER BY UP.EMPLOYID

END
ELSE
BEGIN

      INSERT MBSCUSTOM.dbo.Audit401KDetail
      (
        hid,PNUM, PRDATE, SSN, ytdHours, ytdPay, ytd415
        ,[PT%], [AT%], PTDPay
        ,PFC, CurrPTEE, CurrPTER
        ,CurrAT, Loan
        ,LoanPMT, [ER%], EMPName, Status
        ,EN, HC, DOB, DOH, DOPE, DOT
        ,ADOH, DIV, ADD1
        ,ADD2, City, State, Zip, EmpType, CONV, SP
        ,E401K
      )
      SELECT      @runID, '001111'
      ,            CASE WHEN MCDATEPTEE IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATEPTEE AS datetime))
                        WHEN MCDATEPTER IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATEPTER AS datetime))
                        WHEN MCDATECURRLOAN IS NOT NULL THEN dbo.ConvertDate(CAST(MCDATECURRLOAN AS datetime))
                        ELSE dbo.ConvertDate(GetDate())
                  END
      ,            UP.SOCSCNUM
      ,            CASE WHEN YTDHOURS IS NOT NULL THEN RIGHT(dbo.Convert4V3Int(YTDHOURS),7) ELSE '0000000' END
      ,            CASE WHEN YTDWAGES IS NOT NULL THEN RIGHT(dbo.ConvertS9V2Dec3(YTDWAGES),11) ELSE '00000000000' END,'00000000000'
      ,            CASE WHEN #CurrPTEEAmounts.DEDNPRCT_1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTEEAmounts.DEDNPRCT_1),4) END, '0000'
      ,            CASE WHEN #CurrPayAmounts.PTD_Pay IS NULL THEN '00000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPayAmounts.PTD_Pay),11) END
      ,            CASE WHEN #CurrPTEEAmounts.PFC IS NOT NULL THEN CASE #CurrPTEEAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
                        WHEN #CurrPTERAmounts.PFC IS NOT NULL THEN CASE #CurrPTERAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
                        WHEN #CurrLoanAmounts.PFC IS NOT NULL THEN CASE #CurrLoanAmounts.PFC WHEN '1' THEN '7' WHEN '2' THEN '6' WHEN '3' THEN '4' ELSE '0' END
                        ELSE '0'
                  END
      ,            CASE WHEN #CurrPTEEAmounts.PTEE IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTEEAmounts.PTEE),9) END
      ,            CASE WHEN #CurrPTERAmounts.PTER IS NULL THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTERAmounts.PTER),9) END
      ,            '000000000'
      ,            CASE WHEN #CurrLoanAmounts.LoanPMT > 0 THEN '001' ELSE '000' END
      ,            CASE WHEN #CurrLoanAmounts.LoanPMT IS NULL THEN '000000000' WHEN #CurrLoanAmounts.LoanPMT < 0 THEN '000000000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrLoanAmounts.LoanPMT),9) END  
      ,            CASE WHEN #CurrPTERAmounts.BNFPRCNT_1 IS NULL THEN '0000' ELSE RIGHT(dbo.ConvertS9V2Dec3(#CurrPTERAmounts.BNFPRCNT_1),4) END , dbo.rightpad(HR.EMPNAME)
      ,            HR.HRSTATUS
      ,            '000000'
      ,            HIGHLYCOMP
      ,            dbo.ConvertDate(CAST(UP.BRTHDATE AS datetime))
      ,            dbo.ConvertDate(CAST(UP.STRTDATE AS datetime))
      ,            DOPE
/*      ,            CASE WHEN HR.DOT <> '00000000' THEN dbo.ConvertDate(CAST(HR.DOT AS datetime)) ELSE HR.DOT  END*/
      ,            CASE WHEN HR.DOT IS NOT NULL THEN dbo.ConvertDate(CAST(HR.DOT AS datetime)) ELSE '00000000'  END
      ,            ADOH,@DIV, dbo.rightpad(LTRIM(RTRIM(ADDRESS1)))
      ,            dbo.rightpad(LTRIM(RTRIM(ADDRESS2))), CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(CITY))),18) AS CHAR(18))
      ,            CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(STATE))),2) AS CHAR(2)), CAST(LEFT(dbo.rightpad(LTRIM(RTRIM(ZIPCODE))),9) AS CHAR(9))
      ,            EMPCLASS,'00000000'
      ,            SP
      ,            RIGHT(dbo.ConvertS9V2Dec3(HR.UPRTRXAM),9)
      FROM      LBI.dbo.UPR00100 UP
      /**            ALTER THE INNER JOINS
                  INNER JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
                  INNER JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
                  INNER JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID    **/
                        LEFT JOIN #CurrPayAmounts ON UP.EMPLOYID = #CurrPayAmounts.EMPLOYID
                        LEFT JOIN #YTDInfo ON UP.EMPLOYID = #YTDInfo.EMPLOYID
                        LEFT JOIN #HRInfo HR ON UP.EMPLOYID = HR.EMPLOYID
                        LEFT JOIN #CurrPTEEAmounts ON UP.EMPLOYID = #CurrPTEEAmounts.EMPLOYID
                        LEFT JOIN #CurrPTERAmounts ON UP.EMPLOYID = #CurrPTERAmounts.EMPLOYID
                        LEFT JOIN #CurrLoanAmounts ON UP.EMPLOYID = #CurrLoanAmounts.EMPLOYID
      WHERE      (#CurrPTEEAmounts.PFC = @frequency
                   OR      #CurrPTERAmounts.PFC = @frequency
                   OR #CurrLoanAmounts.PFC = @frequency)
      AND            UP.EMPLOYID <> 'REPRINTED'
      ORDER BY UP.EMPLOYID
END

SET @rowCount = @@ROWCOUNT

--SELECT * FROM #CurrPTEEAmounts
--SELECT * FROM #CurrPTERAmounts
--SELECT * FROM #CurrPayAmounts
--SELECT * FROM #CurrLoanAmounts
--SELECT * FROM #CurrentAmounts
--SELECT * FROM #HRINFO
--SELECT @rowCount 'row Count'
--SELECT * FROM Audit401KDetail

DROP TABLE #CurrPTEEAmounts
DROP TABLE #CurrPTERAmounts
DROP TABLE #CurrPayAmounts
DROP TABLE #CurrLoanAmounts
--DROP TABLE #CurrentAmounts
--DROP TABLE #401KAggregate
DROP TABLE #YTDInfo
DROP TABLE #HRINFO

RETURN 0
SET NOCOUNT OFF
SET ANSI_PADDING OFF

ErrorHandler:
      SELECT @strERR = 'Error in procedure : ' + LTRIM(RTRIM(OBJECT_NAME(@@PROCID))) + '  during step : ' + @strStep + '.  Error Number : ' + CAST(@errNum AS VARCHAR(128))
      RAISERROR (@strERR, 16, 1) WITH LOG
      RETURN 99
GO
#HRINFO is created based on the table LBI.dbo.UPR00100.  Then the following columns are added:
DOT VARCHAR(10)
UPRTRXAM NUMERIC(19,5)

Please post the CREATE TABLE for the table LBI.dbo.UPR00100.  I suspect that is where the problem lies.

Incidentally, you reported 2 problems, I am only addressing the waring message:
 INSERT fails. Warning: The table '#HRINFO' has been created but its maximum row size (8354) exceeds the maximum number of bytes per row (8060).
Avatar of apitech

ASKER

The UPR00100 table isn't created by this code.  This table was installed in SQL in the LBI database when our accounting application was installed.

It's interesting that you should mention the UPRTRXAM column, though.  That first error that you said that you are not addressing speaks to that field ,as the "E401K" field in that error message depends on the results of that field.  But, the UPRTRXAM does not have any NULL values.  So, I don't know why it is throwing up that first error.
>>The UPR00100 table isn't created by this code.<<
I did not intend to imply that it was.  But it is used to create the temp table #HRINFO and that is why inspecting that should reveal the problem.
Avatar of apitech

ASKER

Disregard.....for some mysterious reason, the web application iss working now all of a sudden!

Thanks to all for your responses!!!!!
Fair enough.  I suspect you still have the problem.  But it will only become apparent until a user enters more data than you are currently seeing.

In the meantime, please close this question.
>>But it will only become apparent until a user enters more data than you are currently seeing<<
But I repeat myself #24403638
ASKER CERTIFIED SOLUTION
Avatar of apitech
apitech

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial