Error 'Error evaluating CHECK constraint.' in the validation rule.

[YC]

I have looked in all the tables and cannot find the reason.

 

[Tom van Stiphout]

On Tue, 25 Sep 2007 07:02:02 -0700, YC <[email protected]>
wrote:

You are linked to a sql server table, and you have a CHECK constraint
on one of the fields, and you are violating it.
Assuming SQL Server 2005, run this query:
select * from sys.check_constraints
Or in SQL Server 2000:
select * from sysconstraints
SQL Server Books Online has more info about this.

-Tom.

 

[Pieter Wijnen]

Or
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

For SQLServer 2000, 2005 & Oracle etc, etc

Pieter

 

[Jamie Collins]

Are there *two* spaces between the words 'evaluating' and 'CHECK'? In
my experience, this error is usually the result of exceeding Jet's row
limit (bytes). It seems the engine internally uses CHECK constraints
to implement some of its own rules (very reassuring <g>!) and I
further guess these internal constraints either have a zero length
string name or their name gets removed from the failure message, hence
the double spacing.

FWIW here's some VBA to reproduce the error to which I refer:

Sub RowLimitCheck()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim sql As String
sql = _
"CREATE TABLE Test" & vbCr & "(" & vbCr & "col01 INTEGER" & _
" NOT NULL, " & vbCr & "col02 VARCHAR(255)" & _
" DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'"
& _
" NOT NULL, " & vbCr & "col03 VARCHAR(255)" & _
" DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'"
& _
" NOT NULL, " & vbCr & "col04 VARCHAR(255)" & _
" DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'"
& _
" NOT NULL, " & vbCr & "col05 VARCHAR(255)" & _
" DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'"
& _
" NOT NULL, " & vbCr & "col06 VARCHAR(255)" & _
" DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'"
& _
" NOT NULL, " & vbCr & "col07 VARCHAR(255)" & _
" DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'"
& _
" NOT NULL, " & vbCr & "col08 VARCHAR(255)" & _
" DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'"
& _
" NOT NULL, " & vbCr & "col09 VARCHAR(255)" & _
" DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'"
& _
" NOT NULL" & vbCr & ");"
.Execute sql
sql = _
"INSERT INTO Test (col01) VALUES" & _
" (1);"
.Execute sql
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Jamie Collins]

You are linked to a sql server table, and you have a CHECK constraint
on one of the fields, and you are violating it.

Why do you say that? SQL Server doesn't have 'validation
rules' (mentioned by the OP in the subject line). This being the
Access group, Isn't it more likely that it's a Jet CHECK constraint?

....in which case, I think it is an internal CHECK constraint rather
than a user defined one. A user defined CHECK should return the
constraint name; if you didn't give the constraint a name at design
time then Jet would have made one up for you and show this e.g.

CREATE TABLE Test1
(
col1 DATETIME,
CHECK (DATEADD('YYYY', 99, col1) < #9999-12-31 23:59:59#)
)
;
INSERT INTO Test1 (col1) VALUES (#9999-12-31 23:59:59#)
;

The failure message I see is:

Error evaluating Check_7881380B_B4BA_4AF4 CHECK constraint.
Check_7881380B_B4BA_4AF4

On the other hand, the system CHECK constraint failure messages I've
seen (e.g. exceeding row limit) simply return:

Error evaluating CHECK constraint

with the double space indicating the constraint name placeholder.

Jamie.

--