Rule binding in SQL Server 2000

Posted on 2004-11-17
Last Modified: 2012-05-05
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?
Question by:uelitribelhorn
    LVL 17

    Accepted Solution

    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.

    Author Comment

    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.
    LVL 17

    Expert Comment

    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

    Author Comment

    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 ,
    ) ON [PRIMARY]

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

    LVL 17

    Expert Comment

    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.

    Author Comment

    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.

    Author Comment

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now