Question Stored Procedure/TableAdapter and Failed to enable contraints


Well-known member
Jan 11, 2013
Programming Experience
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:

             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

                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]
                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
                (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

In my application I perform get data on the stored procedure:
                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.

Latest posts

Top Bottom