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

MS-DTC...Some vulnerability info required

hi...
Sorry if i am putting question at wrong place, guide me.
I have tried 2 exploits (available on net) of Distributed
Transaction Coordinator at 4 different 2k machines.
But after execution of exploit, i dont see any change in
behavior of DTC. It works as it was. The target systems
do not have the required patch/service pack.
Kindly put some suggestions or take me to appropriate group.
Take care...bye
Ok, why 2000 and not 2003 R2? Why are you trying to get in? From the inside
of the network or outside? What exactly did you try? What patches did you
think would fix it?
This could be the case where a later patch fixes the issue or your testing
is a little off. Either way - move to 2003!!!!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Umer" <Umer@.discussions.microsoft.com> wrote in message
news:DC4A6615-30F7-44C2-BD81-97D4262B14C4@.microsoft.com...
> hi...
> Sorry if i am putting question at wrong place, guide me.
> I have tried 2 exploits (available on net) of Distributed
> Transaction Coordinator at 4 different 2k machines.
> But after execution of exploit, i dont see any change in
> behavior of DTC. It works as it was. The target systems
> do not have the required patch/service pack.
> Kindly put some suggestions or take me to appropriate group.
> Take care...bye

MSDTC, Active Diretory, DAO, And Transactions

If this is in the wrong forum, my apologies.
Here is the situation. We have a .Net application that someconsultants developed several years ago. They made use of DAO fordatabase access, using some inbuilt classes like ContextUtil to get atransation environment.
Until last weekend, the envorinment was:
Web application on IIS5 / Windows 2000 Advanced Server
Database on SQL2000 on Windows 2000 Advanced Server
We switched over to a new database server environment - a cluster oftwo Windows 2003 servers wth SQL Server 2000. This requiredActive Directory to be set up on the two database server machines.
Everything worked but this particular application - where it goes toupdate the database. It appears (but since I haven't yet beenable to rebuild the .Net solution) that the error - "Object referencenot set to an instance of object." is in fact a spurious error causedwhen the transation tries to roll back.
I have spent about 10 hours on this, and have come to the conclusionthat the fact I have MSDTC involved on a database server that is in anAD domain, while the web server that is issuing the transaction isoutside that domain is the cause of the problem. Now, I don'twant to put AD on the IIS 5 web server - we are migrating this to a newWin2k3/IIS6 server in 5 or 6 weeks time.
Any suggestions? If I have to, I can move the database for thisparticular app back to the non-clustered Windows 2000 server (which isalso outside the AD domain).
Thanks for your indulgance.
Microsoft requirement and good practice is a database Server is just a member server, it should not have anything to do with AD users. If your application is using DAO(data Access object) which was obselete in 1998 you may need MSDTC, the error could be you have not properly configured distributed transaction in SQL Server. Run a search for distributed transaction in the BOL(books online). The advice keep your application from none database users and AD. Hope this helps.