?
Solved

Rule binding in SQL Server 2000

Posted on 2004-11-17
7
Medium Priority
?
921 Views
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?
0
Comment
Question by:uelitribelhorn
  • 4
  • 3
7 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 1500 total points
ID: 12602815
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.
0
 

Author Comment

by:uelitribelhorn
ID: 12603072
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.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12603393
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)
as
begin
      insert into MyNewTable values (@x)
end


exec MyNewProc 'Q'   ---- this fails
exec MyNewProc 'A' --- this succeeds
exec MyNewProc '' --- this succeeds
exec MyNewProc NULL   --- this succeeds
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:uelitribelhorn
ID: 12603887
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'
GO

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



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

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



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

create procedure p_MySp
      .
      .
as
      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'.


0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12604906
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.
0
 

Author Comment

by:uelitribelhorn
ID: 12613329
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:

Statement:
INSERT S_REFERENZ_ZUSTELLADRESSE
         (BETRIEBS_NR, LAUFNR, STEUERJAHR, STEUERFALL, STEUERART_DO, ADRESS_NR, VERWENDUNGSART_DO, ERGAENZUNG_ZUSTELLADRESSE, SACHBEARBEITER_ID_DO)
  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.
0
 

Author Comment

by:uelitribelhorn
ID: 12679337
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

809 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