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 an
d
> 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...
>
>|||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...[vbcol=seagreen]
> 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:
>|||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...
>
>|||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...[vbcol=seagreen]
> 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:
>|||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 yo
u
> 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, sinc
e
> 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...
>
>|||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...[vbcol=seagreen]
> 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:
>