Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2002
    Posts
    229

    Unanswered: Msg "cannot add rows to sysdepends... procedure will still be created"

    Can I get the missing rows added to sysdepends later, when I create procedures in the wrong order, thus getting the "Cannot add rows to sysdepends for the current stored procedure ... The stored procedure will still be created" ?

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Yeah, what he said...(?)

    Quote Originally Posted by Coolberg
    Can I get the missing rows added to sysdepends later, when I create procedures in the wrong order, thus getting the "Cannot add rows to sysdepends for the current stored procedure ... The stored procedure will still be created" ?

    Well???? whatcha guys say?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Sure you can.

    Say that MainProc calls SubProc, but when you create the procs in the database you create MainProc 1st. When you create MainProc, you will get the sysdepends message "Cannot add rows ...". When you create SubProc, sysdepends is **not** updated. If you then drop and add MainProc, sysdepends will be populated with the dependancy information.

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Yeah, that's how I've gotten around a few of the problems created by the scripting/transfers to other databases (mirrors, in my case).

    [RANT]
    However, being the bull-headed son-of-a-db2 programmer that I am, I refuse to do that to every freakin' proc/table/function/etc. in my script. Seems to me that if I use SQL Server to script out a humongous script and then use that script to build a new DB, that I shouldn't have to manually go through the process of dropping and rebuilding each object in the new db.

    Perhaps Uncle Bill wants it that way, but I have always been a problem nephew, so I am trying to figure a more global, one-button solution. I did find this thread on the subject after posting the original query, however. http://www.dbforums.com/t406785.html

    I will take a look at that solution and see how it works.

    Don't get me wrong, I do truly appreciate your response. Sincerely. I just don't have the time and inclination to use the manual drop/readd thang...when that is actually only a tad more desireable than manually writing down the object dependencies on the chalkboard in the hallway.
    [/RANT]

    Thanks again...and sorry for the rant...I just don't like the appearance of bells and whistles that only impotently blow the ball around inside (though they might do so at a high rate of speed), or dog and pony shows consisting of a toy poodle and a plow nag.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Argh...alas...the sp_recompile/sp_refreshview solution still only goes from the individual object perspective...so I still will need to order the objects according to referential sequences before doing the recompiles.

    Still not my silver bullet.

    I guess I'll just requisition a few more chalkboards Thanks again!
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Dependency is resolved at the time of creation of the first execution plan. Thus, - necessary recompile that can be observed while running profiler.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Huh?

    Quote Originally Posted by rdjabarov
    Dependency is resolved at the time of creation of the first execution plan. Thus, - necessary recompile that can be observed while running profiler.
    Are you saying that just running a stored proc will "rebuild" the dependency information in the sysdepends table? If so, then theoretically that means that all I need to do is wait for all the stored procs in the database to run and then my sysdepends table will be all better?

    That would be cool, but it doesn't seem to hold true in my (admittedly limited) experience. I'll have to check it out, but my stuff runs M-F here at work, and yet I still have "missing" or "incomplete" dependencies when I list dependencies in Enterprise Manager *scratching head*

    Am I missing your point? Are you saying the profiler must be run to rebuild the dependencies? *LOL* I guess instead of guessing I'll wait until you clarify your comments...

    As always, thanks!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I THOUGHT you were going after a physical dependency, not the ability to execute a procedure even if sysdepends doesn't contain a reference...but hoped that that would not be the case after all...Oh well, you're write, dependency can only be reestablished if you either re-CREATE or ALTER the parent procedure after the child has been created. SP_RECOMPILE does not do the trick, BTW.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by rdjabarov
    I THOUGHT you were going after a physical dependency, not the ability to execute a procedure even if sysdepends doesn't contain a reference.
    I probably know just enough to be dangerous to myself and those around me but I really just wanted to build the sysdepends table so that when I DISPLAY dependencies of the various objects (i.e., right click->all tasks->display dependencies) it would be accurate.

    I'm not sure which of your situations that desire falls under. sorry if I don't understand...I think it would be the physical dependency that I am after. As far as I know, I've always been able to execute a procedure even if the sysdepends info is bad (i.e., even if the dependency is not displayed in "display dependencies" - which I have understood to get its info from the sysdepends table).

    In any case, I think the bottom line is that there is no easy way to rebuild things in sysdepends so I can be assured that all my dependencies are displayed when I try to see them via right-click...short of the drop/add thing.

    Although, as I recall, if I go into the stored proc and modifiy something (as opposed to drop/add the proc) - I think the dependencies are re-evaluated when I save my changes...at least that's what I recall from earlier playing around with 'em
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's because EM fires ALTER which reevaluates the dependencies, as I mentioned earlier. If you want to achieve the result desired you need to invest into something like DBArtisan, but if you just want to continue bashing EM, - be my guest
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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