|
|
Posted by markjerz@googlemail.com on 01/16/07 12:30
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
Navigation:
[Reply to this message]
|