Rule binding in SQL Server 2000

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.

My questions:

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?

Any ideas?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I cannot replicate your problem i.e. there is no issue with inserting NULL to a column which has a rule such as you give above.
There is a difference between an empty string ('') and NULL, also there is a difference in the way SQL6.5 & SQL2000 handle these empty strings, so perhaps this is where you are getting a problem?

Here's how rules and precendence works. If you create a rule, and then bind it to a data type, then any column of that data type will have the rule assigned to it. If you then bind a rule directly to a column, then this will take precedence over the old rule binding, and so the column will now only be bound to the new rule. If you then remove this binding, the column  will no longer stay bound to any rule. (i.e. the rule that is bound to the data type does not 'come back' in any way when you remove the other rule)
There is no such thing as rule checking order, there can only be one rule that applies to a column. This will be the column-specific rule, rather than the data type rule if both have been bound in the past. When you unbind a rule, you leave the column with no rule applied at all, it will not regress to the rule bound to the data type. So, once you unbind the rule from the column, there will be no rule at all in play, even though the rule is still bound to the datatype, hence you can make any mods you like to the data.

A point to make. Rules are a backward compatability item only. They have been maintained in SQLServer2000 to facilitate backward compatibility with older versions. However, the prefered method to use is to use check constraints. THere is no guarantee that Rules will be supported in future versions of SQLServer, and so, if you are makeing manual changes at this stage, you may be better off migrating your rules to check constratints.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
uelitribelhornAuthor Commented:
Thanks BillAn1, this is a good eplanation of the things I'm concerned with.

In the meantime I've seen that unbinding a rule from a column leaves the column without any rule, as you stated, even if this is not obviously visible in the Enterprise Manager GUI. And binding a rule to a data type binds it to the column as well, as far as I can see in the GUI.

So I leave the rules as they are.
I still can't figure out why an INSERT statement run in a stored procedure cannot insert NULL when in Query Analyzer I can do it. Is it a matter of permissions the app has that executes the procedure?

The value to be inserted by the procedure is hold in a variable of the same type as the column. This variable gets its value by a select from another table/column. When I debug the stored procedure the variable is represented as having NULL, so I assume really NULL, not empty.

Another way could be to expand the rule to "@theValue in ('A', 'S', '') or @theValue IS NULL" which seems to work. But I'd prefer to know the reason of the stored procedure behaviour.
can you share the actual sp code and the error you get?
I cannot replicate your problem - e.g. the following test code I used, I was able to insert NULL no problem :

create rule newrule as @theValue in ('A', 'S', '')
EXEC sp_addtype newtype, 'char(1)', 'NULL'

sp_bindrule newrule, 'newtype'

create table MyNewTable (MyNewCol newtype)
sp_bindrule newrule,'MyNewTable.MyNewCol'

insert into MyNewTable values ('Q') --- this fails
insert into MyNewTable values ('A') --- this succeeds
insert into MyNewTable values ('') --- this succeeds
insert into MyNewTable values (NULL) --- this succeeds

create procedure MyNewProc (@x newtype)
      insert into MyNewTable values (@x)

exec MyNewProc 'Q'   ---- this fails
exec MyNewProc 'A' --- this succeeds
exec MyNewProc '' --- this succeeds
exec MyNewProc NULL   --- this succeeds
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

uelitribelhornAuthor Commented:
Looks very similar to my situation.

To post the real (customers) code is somewhat delicate so I reduced it to the important parts. I hope this is readable.

============= rule def:
create rule [R_MyVar_c1aufz] as @MyVar in ('A', 'S', '')

============= data type def:
EXEC sp_addtype N'MyVar_c1aufz', N'char (1)', N'not null'

EXEC sp_bindrule N'[dbo].[R_MyVar_c1aufz]', N'[MyVar_c1aufz]'

============= table def:
CREATE TABLE [dbo].[MyTable] (
==>     [MyVar] [MyVar_c1aufz] NULL ,

EXEC sp_bindrule N'[dbo].[R_MyVar_c1aufz]', N'[MyTable].[MyVar]'

============= fragments of stored procedure:

create procedure p_MySp
      declare      /* ------------------------------------------------------------ */
            @MyVar            MyVar_c1aufz

============= variables filled up:

      select      @MyVar      = MyVar
      from      MyTable
      where      MyVar      is null

============= insert fails:

      insert      MyTable(MyVar)
      values      (@MyVar)

============= error message:

A column insert or update conflicts with a rule imposed by a previous CREATE RULE
statement. The statement was terminated. The conflict occurred in database 'JUST',
table 'MyTable', column 'MyVar'.

Not sure I can shed much more light.
When I run your sample code, I have no problem inserting a NULL value.
I start off with just one record with value of NULL in the table, then each time I call your stored proc, it inserts one more.
Do you have the latest service patch (SP3) for SQLServer? I can't see anything obvious that would make this not work.
uelitribelhornAuthor Commented:
It seems very much to be a user/login or Windows Version problem.

I got a new situation where the same Insert-Statement behaves different on various machines:

  VALUES (124893, 1, NULL, NULL, NULL, 40590, 1,'' , 'FDS5')

On a Windows NT 4.0 SP6 machine the statement succeeds

On a Windows XP, Sp1 machine I get an error: A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict ocuured in database 'MyDB', Table 'MyTable', column 'STEUERART_DO'.

This column is nullable and has the rule @steuerart in (11, 21, 22, 31)

This is the same situation we discussed before: inserting of NULL value into a nullable column with a rule seems to depend on the envirenment.

I tend to change all rules to include NULL because time runs away to figure out what's going wrong.
uelitribelhornAuthor Commented:
I'm gonna close this question because I don't do any more research on that subject. Thanks anyway, BillAn1, for participating on the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.