SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Failed to enable constraints. One or more rows con
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

macca
Posting Yak Master

Ireland
146 Posts

Posted - 10/09/2013 :  04:39:18  Show Profile  Reply with Quote
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

I get the above error message when I run the following code:

SELECT cheq_id, cheq_daterec, cheq_pername, CAST(cheq_amt As decimal(9,2)) As cheq_amt, cheq_behalf, cheq_behalf1, cheq_behalf2, N.NatBusName, D.Dept_tdp, cheq_recpno, cheq_recpno1, cheq_recpno2
FROM RecCheqs As C
JOIN NatureBusiness AS N
ON N.NatBusID = C.cheq_natbus_id
JOIN laintranet1.dbo.tblDepartment_tdpOLD AS D 
ON C.cheq_dept = D.DeptId_tdp
WHERE cheq_DateRec >=  coalesce(nullif(@DateFrom, ' '),cheq_DateRec) AND cheq_DateRec <= coalesce(nullif(@DateTo, ' '),cheq_DateRec)
AND cheq_dept = coalesce(nullif(@Section , ' '),cheq_dept)
AND cheq_office = @Sdid
ORDER BY cheq_id


@Section could contain the value of 65, 67, or 69 between any of the dates.
@Sdid could contain a string such as 'Tester'

I get the Error message when the @Section value is a number of values between the 2 dates, but if the @Section values are all the same i.e. all 65 or all 67 then there is no Error, it seems to just be an error when there is more than 1 value for @Section.

I have just started getting this error message and I don't know why?
Any ideas ?

Edited by - macca on 10/09/2013 04:45:43

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2013 :  05:43:53  Show Profile  Reply with Quote
I dont think error posted is generated from this statement. The error seems like you're trying to create constraint on a table and its breaking due to availability of conflicting data in the table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 10/09/2013 :  06:13:31  Show Profile  Reply with Quote
Thanks for the reply.
I am running this to feed a datsource .xsd file .. maybe its in this that the error lies.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2013 :  07:02:50  Show Profile  Reply with Quote
no idea..The error has no relation to code part you posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 10/09/2013 :  07:19:52  Show Profile  Reply with Quote
The only thing I can think of is that the Error started to appear after I recreated the table laintranet1.dbo.tblDepartment_tdpOLD which is in the code as
JOIN laintranet1.dbo.tblDepartment_tdpOLD AS D

I had to put Deptid_tdp as the Primary Key on this table.

Maybe this has something to do with it?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2013 :  07:53:41  Show Profile  Reply with Quote
quote:
Originally posted by macca

The only thing I can think of is that the Error started to appear after I recreated the table laintranet1.dbo.tblDepartment_tdpOLD which is in the code as
JOIN laintranet1.dbo.tblDepartment_tdpOLD AS D

I had to put Deptid_tdp as the Primary Key on this table.

Maybe this has something to do with it?


yep...are you sure Deptid_tdp has unique values and does not contain any NULLs?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 10/09/2013 :  09:14:01  Show Profile  Reply with Quote
Thanks for the help Vishak.
It turned out the problem was with the report. One of the field sizes had changed in the new table and this didnt correspond to the field size in the report so when I set the field size in the report to the same as that of the DB then the Error disappeared.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000