Dependancy problem? 
Author Message
 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 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




AS
 SET NOCOUNT ON


 -- Add new application
 INSERT INTO Applications (Description, DeptOwner, URL, Active)

 -- Get the new ID number

 -- Add a new column

DEFAULT 0 WITH VALUES')

CREATE PROCEDURE spDeleteApplication

AS
 SET NOCOUNT ON

 -- Remove the approriate column from the Access Table


 -- Remove the Application
 DELETE FROM Applications



Fri, 06 Jan 2006 17:49:57 GMT
 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

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 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




AS
 SET NOCOUNT ON


 -- Add new application
 INSERT INTO Applications (Description, DeptOwner, URL, Active)

 -- Get the new ID number

 -- Add a new column

DEFAULT 0 WITH VALUES')

CREATE PROCEDURE spDeleteApplication

AS
 SET NOCOUNT ON

 -- Remove the approriate column from the Access Table


 -- Remove the Application
 DELETE FROM Applications



Fri, 06 Jan 2006 17:55:57 GMT
 Dependancy problem?
You appear to be making the mistake of putting making a fact into a column
in a table. Normalise your design and you won't have the problem you
describe. You didn't explain what the Access table contains but it looks
like what you need is a joining table to create a many-to-many relationship
between Access and Applications. The table you have at the moment can be
implemented as a cross-tabular view.

If you need more help, post the DDL for both tables and some sample data as
INSERT statements.

--
David Portas
------------
Please reply only to the newsgroup
--



Fri, 06 Jan 2006 18:02:14 GMT
 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


Quote:
> 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 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




> AS
>  SET NOCOUNT ON


>  -- Add new application
>  INSERT INTO Applications (Description, DeptOwner, URL, Active)

>  -- Get the new ID number

>  -- Add a new column

int
> DEFAULT 0 WITH VALUES')

> CREATE PROCEDURE spDeleteApplication

> AS
>  SET NOCOUNT ON

>  -- Remove the approriate column from the Access Table


>  -- Remove the Application
>  DELETE FROM Applications




Fri, 06 Jan 2006 18:55:19 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. ado dependancy files

2. Package Dependancy Order

3. HELP: Can cut wizard dependancy!

4. St Proc Dependancy Tree

5. Setup Wizard warning (dependancy files)

6. missing dependancy?

7. Enterprise Manager & scripting database - dependancy

8. Stored procedure dependancy on tables, views and other stored procedures

9. Domain Functional Dependancy?

10. Dependancy (net logon) doesn't want to run

11. ERROR 1059: CIRCULAR SERVICE DEPENDANCY WAS SPECIFIED ON STARTUP

12. user dependancy odbc connection


 
Powered by phpBB® Forum Software