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" ?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?|||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.|||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).

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. ;)

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.|||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|||Dependency is resolved at the time of creation of the first execution plan. Thus, - necessary recompile that can be observed while running profiler.|||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!|||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.|||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|||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 ;)