Stored Procedure/TableAdapter and Failed to enable contraints

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:

Code:

---

                  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
                                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
                                LEFT JOIN TEMSNPH n ON e.empl_id = n.nph_empl_id
                                LEFT JOIN TEMSWSUM w on e.empl_id = w.wsum_empl_id
                                LEFT 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
                                LEFT 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_id is not null
                               
                  DROP TABLE #t
          END

---

In my application I perform get data on the stored procedure:

Code:

---

                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.

Firstly, GetData returns a new DataTable so there's no point creating a new EMS_DS.getABS4BARDataTable yourself first and then discarding it when you call GetData. Only create an object if you intend to use it.

As for the issue, one way to debug might be to create your own DataTable and populate it using the same sproc. You can then examine the data it contains and see where there's a value that would violate a constraint that exists in your typed DataTable. It can't be a foreign key for a standalone DataTable and it seems more likely to be a null rather than a non-unique value.

On your first point would I just do dim absdt as new datatable?

I think i figured out the problem, in the middle of testing it. I think i was missing the where wsum_yr = @yr so it was returning multiple rows from that table. Checking it now.

Quote:

---

Originally Posted by jmcilhinney

Firstly, GetData returns a new DataTable so there's no point creating a new EMS_DS.getABS4BARDataTable yourself first and then discarding it when you call GetData. Only create an object if you intend to use it.

As for the issue, one way to debug might be to create your own DataTable and populate it using the same sproc. You can then examine the data it contains and see where there's a value that would violate a constraint that exists in your typed DataTable. It can't be a foreign key for a standalone DataTable and it seems more likely to be a null rather than a non-unique value.

---

Well that didnt solve it. No idea what is going on.

So how can i fill the datatable to debug like you suggested when i get that error. It won't populate the datatable because of the error, no?

So I have traced it down to something with the temp table and stored procedure within stored procedure.

I don't use stored procedures but I had that error many times and it usually means that I have a record in one of the child tables that is not in the master table.

example,

Master: Customers
Child: CustomerOrders

So if you have a record in the CustomerOrders with a Customer ID that is not in the Customers table, you would get that error. But like I said, I don't use SP so maybe that's your problem.