Question Stored Procedure/TableAdapter and Failed to enable contraints

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
I have been struggling for several days now trying to figure out why I am getting the failed to enable constraints, one or more rows contain values violating non-null, unique, or foreign-key constraints when running my code. I have a stored procedure in sql server, part of it below:

VB.NET:
          BEGIN
             CREATE TABLE #t
             (
                    empl_id               VARCHAR(20),
                    empl_cctr_id           INTEGER,
                    yr                   DECIMAL(4,0),
                    rhrs                   DECIMAL(8,2),
                    othrs                   DECIMAL(8,2),
                    x10210               DECIMAL(8,2),
                    x10220               DECIMAL(8,2),
                    x103xx               DECIMAL(8,2),
                    x10303                   DECIMAL(8,2),
                    x104xx               DECIMAL(8,2),
                    x105xx               DECIMAL(8,2),
                    x106xx               DECIMAL(8,2),
                    x109xx               DECIMAL(8,2),
                    x10901               DECIMAL(8,2),
                    other                   DECIMAL(8,2),
                    nhrs                   DECIMAL(8,2),
                    tothrs               DECIMAL(8,2)
             )

             INSERT INTO #t (empl_id, empl_cctr_id, yr, rhrs, othrs, x10210, x10220, x103xx, x10303, x104xx, x105xx, x106xx, x109xx, x10901, other, nhrs, tothrs)
             EXEC getSMETDetailsAll @yr, 0, 55, @mgr

             --SELECT * FROM #t

             SELECT
                e.empl_last_nm + ', ' + e.empl_first_nm + ' ' + e.empl_middle_init            AS Employee,
                e.empl_id                                                            AS [Empl ID],
                e.empl_myid                                                        AS MyID,
                ISNULL(s.Scheduled,0)                                                AS Scheduled,
                ISNULL(n.nph_vac_used,0)                                                AS [PTO-Used],
                ISNULL(w.wsum_nph10220,0)                                            AS Holiday,
                ISNULL(w.wsum_nph109xx,0)                                            AS [109xx],
                ISNULL(w.wsum_other,0)                                                AS Misc,
                ISNULL(s.Scheduled - (n.nph_vac_used + w.wsum_nph10220 + w.wsum_other + 
                    w.wsum_nph109xx + n.nph_fmla + n.nph_mil + n.nph_disability),0)            AS Net,
                ISNULL(n.nph_fmla,0)                                                AS FMLA,
                ISNULL(n.nph_mil,0)                                                    AS Military,
                ISNULL(n.nph_disability,0)                                            AS Disability,
                ISNULL(convert(decimal(8,2),((t.rhrs + t.othrs)  / (s.pp_cnt * 40)) * 100),0)    AS [Productivity Index]
             FROM
                TEMSEMPL e
                INNER JOIN TEMSNPH n ON e.empl_id = n.nph_empl_id
                INNER JOIN TEMSWSUM w on e.empl_id = w.wsum_empl_id
                INNER JOIN
                    (SELECT hist_empl_id, hist_yr, max(convert(integer,hist_pp)) - min(convert(integer,hist_pp))  + 1 as pp_cnt, 
                          (max(convert(integer,hist_pp)) - min(convert(integer,hist_pp))  + 1) * 40 AS Scheduled
                    FROM      TEMSHIST
                    WHERE      hist_yr = @yr
                    GROUP BY hist_empl_id, hist_yr) s
                ON s.hist_empl_id = e.empl_id 
                INNER JOIN 
                    (SELECT * from #t) t
                ON t.empl_id = e.empl_id
             WHERE
                (e.empl_stat_id IN (1, 2, 3, 4, 6, 8)) AND
                n.nph_payyr = @yr and e.empl_mgr_id = @mgr and e.empl_mgr_id is not null
                
          DROP TABLE #t
       END

In my application I perform get data on the stored procedure:
VB.NET:
                Dim absdt As New EMS_DS.getABS4BARDataTable
                Dim absta As New EMS_DSTableAdapters.getABS4BARTableAdapter
                absdt = absta.GetData(BaseYr, managerLUE.EditValue)

On the getdata line i get the failed to enable constraints. There shouldnt be any constraints. I removed the primary key that the data designer created when I added the table adapter (something i saw when I searched for a solution prior to posting). I tried turning off enforce constraints, but that didnt work. Ive tried changing the statement to left join, inner join, etc.

If i execute the stored procedure in sql server management studio, it works just fine. No data is missing.

Please help me figure this out.
 
Top Bottom