Rule binding in SQL Server 2000
Posted on 2004-11-17
I am migrating a DB from SQL Server 6.5 to SQL Server 2000.
On the 6.5 Database I found a table having a rule bound to a column and also bound to the user defined datatipe of this column. The column is nullable, the rule is: @theValue in ('A', 'S', '')
Updates/Inserts with a NULL value from inside of a stored procedure worked fine on 6.5. The rule is also checked whenever I manipulate data in a SQL Query session (logged on as sa).
On the SQL Server 2000 I cannot update/insert the Value NULL. I get an error stating a conflict with a rule on this column.
On the SQL Server 2000 I tried the following:
- unbind the rule from the column
- leave the rule bound to the data type
- insert/update any values outside the rule in SQL Query Analyzer session logged on as a user, not sa. THIS WORKS!!! No rule checking at all.
1. does it make sense to bind a rule to both the data type and the column?
2. why doesn't allow SQL server 2000 to insert/update a NULL value to a nullable column?
3. is the rule checking order in an SQL Query Analyzer session different than in a stored procedure?