Professional Web Applications Themes

Dependancy problem? - Microsoft SQL / MS SQL Server

Hey all, Ran into a problem I am having trouble figuring out. I am doing this on a webpage as part of an Intranet User/Application maintenance interface. Quick background -- I have two tables: Access, and Applications. When I add an app to the Applications table I take the Identity number of that add and us it to create a column in the Access table naming a column "A[#]" (i.e.: A5, A17, etc.). The stored procedure is "spCreateNewApplication" attached at the end of this post. The problem comes in when I try to use my delete stored procedure when I ...

  1. #1

    Default Dependancy problem?

    Hey all,

    Ran into a problem I am having trouble figuring out. I am doing this on
    a webpage as part of an Intranet User/Application maintenance interface.
    Quick background -- I have two tables: Access, and Applications. When I
    add an app to the Applications table I take the Identity number of that
    add and us it to create a column in the Access table naming a column "A[#]"
    (i.e.: A5, A17, etc.). The stored procedure is "spCreateNewApplication"
    attached at the end of this post.
    The problem comes in when I try to use my delete stored procedure when I
    need to remove an app. I need to not only delete it from the Applications
    table, but also need to drop the column that was made in the Access table
    (this sp, "spDeleteApplication", is also attached at the end of this post).
    When the spDeleteApplication is run, I get the following error returned:
    "-2147217900 - The object 'DF__Access__A17__693CA210' is dependent on column
    'A17'."
    Two things here:
    1) I am not sure I understand what I am being told here -- what is the
    DF and 693CA210 referring to?
    and
    2) What is it that I am doing wrong, or not doing, to allow the dropping
    of the column?

    I have tried looking on the MSKB for this error yet not seeming to find
    anything that points me to something familiar. I have also looked over the
    SQL-BOL on "dependencies" and not seeing anything there that I recognize as
    helpful.
    If someone could give me a clue, a pointer, or a suggestion here, I sure
    would appreciate it. Thanks in advance.

    -- Andrew

    SP's I am using:

    CREATE PROCEDURE spCreateNewApplication
    Description varchar(50),
    DeptOwner varchar(35),
    URL varchar(100),
    Active int
    AS
    SET NOCOUNT ON
    DECLARE AppID int
    DECLARE SQL nvarchar(100)
    -- Add new application
    INSERT INTO Applications (Description, DeptOwner, URL, Active)
    VALUES (Description, DeptOwner, URL, Active)
    -- Get the new ID number
    SET AppID = Identity
    -- Add a new column
    SET SQL = ('ALTER TABLE Access ADD A' + Convert(nvarchar, AppID) + ' int
    DEFAULT 0 WITH VALUES')
    EXEC sp_executesql SQL


    CREATE PROCEDURE spDeleteApplication
    AppID int
    AS
    SET NOCOUNT ON
    DECLARE SQL nvarchar(100)
    -- Remove the approriate column from the Access Table
    SET SQL = ('ALTER TABLE Access DROP COLUMN A' + Convert(nvarchar, AppID))
    EXEC sp_executesql SQL
    -- Remove the Application
    DELETE FROM Applications
    WHERE AppID = AppID


    Andrew Guest

  2. #2

    Default Re: Dependancy problem?

    Rethink your design. Why not have your Access table have a FK to the Applications table and then insert one row into Access for each type of access that you want. When you need to drop the app, then you do something like:

    delete Access
    where AppID = 123

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew" <pinal.az.us> wrote in message news:#phx.gbl...
    Hey all,

    Ran into a problem I am having trouble figuring out. I am doing this on
    a webpage as part of an Intranet User/Application maintenance interface.
    Quick background -- I have two tables: Access, and Applications. When I
    add an app to the Applications table I take the Identity number of that
    add and us it to create a column in the Access table naming a column "A[#]"
    (i.e.: A5, A17, etc.). The stored procedure is "spCreateNewApplication"
    attached at the end of this post.
    The problem comes in when I try to use my delete stored procedure when I
    need to remove an app. I need to not only delete it from the Applications
    table, but also need to drop the column that was made in the Access table
    (this sp, "spDeleteApplication", is also attached at the end of this post).
    When the spDeleteApplication is run, I get the following error returned:
    "-2147217900 - The object 'DF__Access__A17__693CA210' is dependent on column
    'A17'."
    Two things here:
    1) I am not sure I understand what I am being told here -- what is the
    DF and 693CA210 referring to?
    and
    2) What is it that I am doing wrong, or not doing, to allow the dropping
    of the column?

    I have tried looking on the MSKB for this error yet not seeming to find
    anything that points me to something familiar. I have also looked over the
    SQL-BOL on "dependencies" and not seeing anything there that I recognize as
    helpful.
    If someone could give me a clue, a pointer, or a suggestion here, I sure
    would appreciate it. Thanks in advance.

    -- Andrew

    SP's I am using:

    CREATE PROCEDURE spCreateNewApplication
    Description varchar(50),
    DeptOwner varchar(35),
    URL varchar(100),
    Active int
    AS
    SET NOCOUNT ON
    DECLARE AppID int
    DECLARE SQL nvarchar(100)
    -- Add new application
    INSERT INTO Applications (Description, DeptOwner, URL, Active)
    VALUES (Description, DeptOwner, URL, Active)
    -- Get the new ID number
    SET AppID = Identity
    -- Add a new column
    SET SQL = ('ALTER TABLE Access ADD A' + Convert(nvarchar, AppID) + ' int
    DEFAULT 0 WITH VALUES')
    EXEC sp_executesql SQL


    CREATE PROCEDURE spDeleteApplication
    AppID int
    AS
    SET NOCOUNT ON
    DECLARE SQL nvarchar(100)
    -- Remove the approriate column from the Access Table
    SET SQL = ('ALTER TABLE Access DROP COLUMN A' + Convert(nvarchar, AppID))
    EXEC sp_executesql SQL
    -- Remove the Application
    DELETE FROM Applications
    WHERE AppID = AppID



    Tom Guest

  3. #3

    Default Re: Dependancy problem?

    I was able to fix the problem I was encountering. It seems that the
    setting of a default value when creating the Access column put a Constraint
    onto the column that would not allow it to be dropped later. So, I simply
    removed that part of the Add stored procedure, replacing it with a second
    line that sets the new column's values to zero. By not creating
    (unintentionally), the constraint, the column could then be dropped without
    any fuss.
    Thanks Tom and David for your quick replies.

    -- Andrew


    "Andrew" <pinal.az.us> wrote in message
    news:%phx.gbl... 
    on 

    "A[#]" 

    post). 
    returned: 
    column 
    dropping 
    find 
    the 
    as 
    sure 
    int 
    AppID)) 


    Andrew Guest

Similar Threads

  1. Replies: 0
    Last Post: August 23rd, 11:56 AM
  2. Replies: 2
    Last Post: July 17th, 07:27 AM
  3. Replies: 0
    Last Post: July 14th, 12:50 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139