Posted by othellomy on 01/16/07 12:24

just drop table and recreate it with new rules and then import.

markjerz@googlemail.com wrote:
> Hi Erland,
>
> I am now having problems updating the rule to allow nulls. It's
> basically telling me that it can not drop the rule because it is bound
> to one or more columns.
>
> I'm not actually dropping the rule just opening it's properties in
> enterprise manager and then clicking apply once I've changed it.
>
> Any ideas? It seems a little stupid that I would have to unbind it
> first then change it and rebind it. It's currently bound to a lot of
> columns.
>
> Mark
>
>
> Erland Sommarskog wrote:
>
> > (markjerz@googlemail.com) writes:
> > > Now, table2 has a rule on various columns:
> > >
> > > @CHARACTER IN ('Y','N')
> > >
> > > but the column allows nulls, in the design view is says so anyway.
> > >
> > > When I run this query I get:
> > >
> > > 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 'database', table 'table', column 'column'.
> > > The statement has been terminated.
> > >
> > > Obviously, I've changed the names of everything.
> > >
> > > The only data in those columns which could possibly conflict with the
> > > rule is the NULL value. Any ideas why this doesn't work?
> >
> > In my opinion it is a bug, but it has been way since SQL 7 (SQL 6.5
> > did it right), and Microsoft considers rules to be a deprecated feature,
> > so I have no hope for a fix.
> >
> > The workaround is to write the rule so that it explicitly permits NULL.
> >
> > As for the deprecation - binding rules directly to table columns is a poor
> > idea; use constraints instead. However, in my opinion Microsoft has
> > failed to understand that binding rules to user-defined types is an
> > asset, and for which they do not have any replacement.
> >
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> >
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]