Results 1 to 7 of 7

Thread: Stored Procedure/TableAdapter and Failed to enable contraints

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Mar 2017
    Posts
    18

    Angry 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.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,300

    Re: Stored Procedure/TableAdapter and Failed to enable contraints

    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.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Mar 2017
    Posts
    18

    Re: Stored Procedure/TableAdapter and Failed to enable contraints

    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 View Post
    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.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Mar 2017
    Posts
    18

    Re: Stored Procedure/TableAdapter and Failed to enable contraints

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

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Mar 2017
    Posts
    18

    Re: Stored Procedure/TableAdapter and Failed to enable contraints

    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?

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Mar 2017
    Posts
    18

    Re: Stored Procedure/TableAdapter and Failed to enable contraints

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

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Stored Procedure/TableAdapter and Failed to enable contraints

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width