Droppin rule in SQL Server 2000

I am trying to drop a rule but I am getting the following error message:
"The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
more column".
I tried to run sp_unbindrule but it gave me this error message: The data
type "RULE_NAME" does not exist.
How can I resolve this? Thank you in advanceA rule can be bound to either a particular column or to a user defined
datatype. What is the name of the rule? Also what is the name the table and
the column?
Can you post the syntax you were using?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>I am trying to drop a rule but I am getting the following error message:
> "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
> more column".
> I tried to run sp_unbindrule but it gave me this error message: The data
> type "RULE_NAME" does not exist.
> How can I resolve this? Thank you in advance|||Thanks for your response Kalen.
I could only get some info using sp_help stored procedure. It says owner is
dbo and type = rule.
It is using the table tblEmpType. I am using the syntax as
DROP RULE [dbo].[Rule_Sim_App]
I forgot to mention this in my previous post. When I check in EM -->
Database --> Rules tab, I do not see any rule created by this name
Rule_Sim_App.
Please advise.
"Kalen Delaney" wrote:
> A rule can be bound to either a particular column or to a user defined
> datatype. What is the name of the rule? Also what is the name the table and
> the column?
> Can you post the syntax you were using?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >I am trying to drop a rule but I am getting the following error message:
> >
> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
> > more column".
> >
> > I tried to run sp_unbindrule but it gave me this error message: The data
> > type "RULE_NAME" does not exist.
> >
> > How can I resolve this? Thank you in advance
>
>|||What syntax are you using to try to unbind the rule
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> Thanks for your response Kalen.
> I could only get some info using sp_help stored procedure. It says owner
> is
> dbo and type = rule.
> It is using the table tblEmpType. I am using the syntax as
> DROP RULE [dbo].[Rule_Sim_App]
> I forgot to mention this in my previous post. When I check in EM -->
> Database --> Rules tab, I do not see any rule created by this name
> Rule_Sim_App.
> Please advise.
> "Kalen Delaney" wrote:
>> A rule can be bound to either a particular column or to a user defined
>> datatype. What is the name of the rule? Also what is the name the table
>> and
>> the column?
>> Can you post the syntax you were using?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >I am trying to drop a rule but I am getting the following error message:
>> >
>> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one
>> > or
>> > more column".
>> >
>> > I tried to run sp_unbindrule but it gave me this error message: The
>> > data
>> > type "RULE_NAME" does not exist.
>> >
>> > How can I resolve this? Thank you in advance
>>|||sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
data type "RULE_NAME" does not exist.
Thanks again
----
"Kalen Delaney" wrote:
> What syntax are you using to try to unbind the rule
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> > Thanks for your response Kalen.
> >
> > I could only get some info using sp_help stored procedure. It says owner
> > is
> > dbo and type = rule.
> >
> > It is using the table tblEmpType. I am using the syntax as
> > DROP RULE [dbo].[Rule_Sim_App]
> >
> > I forgot to mention this in my previous post. When I check in EM -->
> > Database --> Rules tab, I do not see any rule created by this name
> > Rule_Sim_App.
> >
> > Please advise.
> >
> > "Kalen Delaney" wrote:
> >
> >> A rule can be bound to either a particular column or to a user defined
> >> datatype. What is the name of the rule? Also what is the name the table
> >> and
> >> the column?
> >> Can you post the syntax you were using?
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >> >I am trying to drop a rule but I am getting the following error message:
> >> >
> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one
> >> > or
> >> > more column".
> >> >
> >> > I tried to run sp_unbindrule but it gave me this error message: The
> >> > data
> >> > type "RULE_NAME" does not exist.
> >> >
> >> > How can I resolve this? Thank you in advance
> >>
> >>
> >>
>
>|||Hi David
Please check the syntax in BOL for sp_unbindrule. The parameter is what you
are unbinding from, either a user defined type or a 'tablename.columnname'.
Since there can only be one rule bound to anything, there is no ambiguity.
The message is indicating that it thinks Rule_Sim_App is a type name, since
it is not in the format of a table and column.
You need to find the name of the table and the column the rule is bound to,
and then use them in the sp_unbindrule statement.
This query might give you a start:
select name as col_name, object_name(id) as table_name from syscolumns
where domain = object_id ('Rule_Sim_App')
Once you have the table and column, you can run sp_unbindrule:
exec sp_unbindrule 'tablename.columnname'
Again, you do not use the name of the rule when calling sp_unbindrule.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
> sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
> data type "RULE_NAME" does not exist.
> Thanks again
> ----
> "Kalen Delaney" wrote:
>> What syntax are you using to try to unbind the rule
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
>> > Thanks for your response Kalen.
>> >
>> > I could only get some info using sp_help stored procedure. It says
>> > owner
>> > is
>> > dbo and type = rule.
>> >
>> > It is using the table tblEmpType. I am using the syntax as
>> > DROP RULE [dbo].[Rule_Sim_App]
>> >
>> > I forgot to mention this in my previous post. When I check in EM -->
>> > Database --> Rules tab, I do not see any rule created by this name
>> > Rule_Sim_App.
>> >
>> > Please advise.
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> A rule can be bound to either a particular column or to a user defined
>> >> datatype. What is the name of the rule? Also what is the name the
>> >> table
>> >> and
>> >> the column?
>> >> Can you post the syntax you were using?
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.solidqualitylearning.com
>> >>
>> >>
>> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >> >I am trying to drop a rule but I am getting the following error
>> >> >message:
>> >> >
>> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to
>> >> > one
>> >> > or
>> >> > more column".
>> >> >
>> >> > I tried to run sp_unbindrule but it gave me this error message: The
>> >> > data
>> >> > type "RULE_NAME" does not exist.
>> >> >
>> >> > How can I resolve this? Thank you in advance
>> >>
>> >>
>> >>
>>|||Bull's eye Kalen. I really appreciate it.
I have asked the developer to use CHECK constraint instead of using bind
rule objects. Is check constraint easy to administer than bind rule objects?
Thanks again ...
"Kalen Delaney" wrote:
> Hi David
> Please check the syntax in BOL for sp_unbindrule. The parameter is what you
> are unbinding from, either a user defined type or a 'tablename.columnname'.
> Since there can only be one rule bound to anything, there is no ambiguity.
> The message is indicating that it thinks Rule_Sim_App is a type name, since
> it is not in the format of a table and column.
> You need to find the name of the table and the column the rule is bound to,
> and then use them in the sp_unbindrule statement.
> This query might give you a start:
> select name as col_name, object_name(id) as table_name from syscolumns
> where domain = object_id ('Rule_Sim_App')
> Once you have the table and column, you can run sp_unbindrule:
> exec sp_unbindrule 'tablename.columnname'
> Again, you do not use the name of the rule when calling sp_unbindrule.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
> > sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
> > data type "RULE_NAME" does not exist.
> >
> > Thanks again
> > ----
> >
> > "Kalen Delaney" wrote:
> >
> >> What syntax are you using to try to unbind the rule
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> >> > Thanks for your response Kalen.
> >> >
> >> > I could only get some info using sp_help stored procedure. It says
> >> > owner
> >> > is
> >> > dbo and type = rule.
> >> >
> >> > It is using the table tblEmpType. I am using the syntax as
> >> > DROP RULE [dbo].[Rule_Sim_App]
> >> >
> >> > I forgot to mention this in my previous post. When I check in EM -->
> >> > Database --> Rules tab, I do not see any rule created by this name
> >> > Rule_Sim_App.
> >> >
> >> > Please advise.
> >> >
> >> > "Kalen Delaney" wrote:
> >> >
> >> >> A rule can be bound to either a particular column or to a user defined
> >> >> datatype. What is the name of the rule? Also what is the name the
> >> >> table
> >> >> and
> >> >> the column?
> >> >> Can you post the syntax you were using?
> >> >>
> >> >> --
> >> >> HTH
> >> >> Kalen Delaney, SQL Server MVP
> >> >> www.solidqualitylearning.com
> >> >>
> >> >>
> >> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >> >> >I am trying to drop a rule but I am getting the following error
> >> >> >message:
> >> >> >
> >> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to
> >> >> > one
> >> >> > or
> >> >> > more column".
> >> >> >
> >> >> > I tried to run sp_unbindrule but it gave me this error message: The
> >> >> > data
> >> >> > type "RULE_NAME" does not exist.
> >> >> >
> >> >> > How can I resolve this? Thank you in advance
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Good idea. Bound rules will be going away in a future version.
Check constraints may be easier to keep track of since they are attached to
a single table.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:65F55A05-C4DC-4E08-803A-FE527C4506A4@.microsoft.com...
> Bull's eye Kalen. I really appreciate it.
> I have asked the developer to use CHECK constraint instead of using bind
> rule objects. Is check constraint easy to administer than bind rule
> objects?
> Thanks again ...
>
> "Kalen Delaney" wrote:
>> Hi David
>> Please check the syntax in BOL for sp_unbindrule. The parameter is what
>> you
>> are unbinding from, either a user defined type or a
>> 'tablename.columnname'.
>> Since there can only be one rule bound to anything, there is no
>> ambiguity.
>> The message is indicating that it thinks Rule_Sim_App is a type name,
>> since
>> it is not in the format of a table and column.
>> You need to find the name of the table and the column the rule is bound
>> to,
>> and then use them in the sp_unbindrule statement.
>> This query might give you a start:
>> select name as col_name, object_name(id) as table_name from syscolumns
>> where domain = object_id ('Rule_Sim_App')
>> Once you have the table and column, you can run sp_unbindrule:
>> exec sp_unbindrule 'tablename.columnname'
>> Again, you do not use the name of the rule when calling sp_unbindrule.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
>> > sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
>> > data type "RULE_NAME" does not exist.
>> >
>> > Thanks again
>> > ----
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> What syntax are you using to try to unbind the rule
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.solidqualitylearning.com
>> >>
>> >>
>> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
>> >> > Thanks for your response Kalen.
>> >> >
>> >> > I could only get some info using sp_help stored procedure. It says
>> >> > owner
>> >> > is
>> >> > dbo and type = rule.
>> >> >
>> >> > It is using the table tblEmpType. I am using the syntax as
>> >> > DROP RULE [dbo].[Rule_Sim_App]
>> >> >
>> >> > I forgot to mention this in my previous post. When I check in EM -->
>> >> > Database --> Rules tab, I do not see any rule created by this name
>> >> > Rule_Sim_App.
>> >> >
>> >> > Please advise.
>> >> >
>> >> > "Kalen Delaney" wrote:
>> >> >
>> >> >> A rule can be bound to either a particular column or to a user
>> >> >> defined
>> >> >> datatype. What is the name of the rule? Also what is the name the
>> >> >> table
>> >> >> and
>> >> >> the column?
>> >> >> Can you post the syntax you were using?
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> Kalen Delaney, SQL Server MVP
>> >> >> www.solidqualitylearning.com
>> >> >>
>> >> >>
>> >> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >> >> >I am trying to drop a rule but I am getting the following error
>> >> >> >message:
>> >> >> >
>> >> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound
>> >> >> > to
>> >> >> > one
>> >> >> > or
>> >> >> > more column".
>> >> >> >
>> >> >> > I tried to run sp_unbindrule but it gave me this error message:
>> >> >> > The
>> >> >> > data
>> >> >> > type "RULE_NAME" does not exist.
>> >> >> >
>> >> >> > How can I resolve this? Thank you in advance
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>