Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: SQL Server Msg 3716 - rule cannot be dropped because it is bound

    Hello,

    I created a database rule on a SQL Server 2000 database usng:

    create rule R_positive_shortint
    as
    @positive_shortint >= 1
    GO

    execute sp_bindrule R_positive_shortint, T_POSITIVE_SHORTINT
    GO

    I am attempting to drop a database rule with the statement:

    IF EXISTS (SELECT name, type
    FROM sysobjects
    WHERE sysobjects.type = 'R' and
    sysobjects.name = 'R_positive_shortint')
    BEGIN
    execute sp_unbindrule T_POSITIVE_SHORTINT

    drop rule R_positive_shortint
    END
    GO

    And I receive a message from the server:

    Server: Msg 3716, Level 16, State 1, Line 1
    The rule 'R_positive_shortint' cannot be dropped because it is bound to one or more column.

    I noticed that this statement completes on a db where the columns of data type T_POSITIVE_SHORTINT contain no data, but fails on a database where columns of type T_POSITIVE_SHORTINT contain data.

    Any ideas about how to solve this?I need the SQL to be conditional

    Thanks for looking and any suggestions

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: SQL Server Msg 3716 - rule cannot be dropped because it is bound

    It works for table with some data (just for sure).

    EXEC sp_addtype price, 'money', 'NOT NULL'
    go
    create rule [price2] as @range >= 10 AND @range < 20
    go
    EXEC sp_bindrule 'price2', 'price'
    go
    CREATE TABLE [dbo].[t1] (
    [id] [int] NULL ,
    [code] [varchar] (10),
    [price] [price] NULL
    )
    GO
    sp_unbindrule price
    go
    drop rule price2
    go

    In your case you unbind rule from first object -but may be exists another object with this rule.

    IF EXISTS (SELECT name, type
    FROM sysobjects
    WHERE sysobjects.type = 'R' and
    sysobjects.name = 'R_positive_shortint')
    BEGIN
    -----------------------------
    SELECT count(*) -- you will see how many objects do have this rule
    FROM sysobjects
    WHERE sysobjects.type = 'R' and
    sysobjects.name = 'R_positive_shortint'
    -----------------------------
    execute sp_unbindrule T_POSITIVE_SHORTINT

    drop rule R_positive_shortint
    END
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •