Posted by othellomy on 01/17/07 05:03

If you have 100s of tables with a faulty rule then you should drop the
whole database and recreate them with a correct rule. It should not
take more than a day to recreate 100s of tables correctly

markjerz@googlemail.com wrote:
> That's not even slightly viable. I have well over a hundred tables
> using that rule with a lot of data in them. I've read something about
> updating syscomments table??? Not sure of the exact procedure though so
> any help would be good.
>
> Thanks.
>
>
> othell...@yahoo.com wrote:
>
> > 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]