Search results matching tags 'Automation', 'Linked server', and 'Perl'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Automation,Linked+server,Perl&orTags=0Search results matching tags 'Automation', 'Linked server', and 'Perl'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Find the complete call tree for a stored procedurehttp://sqlblog.com/blogs/linchi_shea/archive/2009/10/22/find-the-complete-call-tree-for-a-stored-procedure.aspxFri, 23 Oct 2009 03:11:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:18173Linchi Shea<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">Would it be nice to print out the complete call tree of a stored procedure? By <I style="mso-bidi-font-style:normal;">complete call tree</I>, I mean the following:<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt 0.5in;TEXT-INDENT:-27pt;tab-stops:.5in;"><SPAN style="FONT-WEIGHT:normal;FONT-FAMILY:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><SPAN style="mso-list:Ignore;"><FONT size=3>·</FONT><SPAN style="FONT:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN></SPAN></SPAN><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">At the very top level, the call tree should identify all the procedures that are called directly.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt 0.5in;TEXT-INDENT:-27pt;tab-stops:.5in;"><SPAN style="FONT-WEIGHT:normal;FONT-FAMILY:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><SPAN style="mso-list:Ignore;"><FONT size=3>·</FONT><SPAN style="FONT:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN></SPAN></SPAN><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">For any given level, the next level of the call tree should identify all the procedures called by the procedures at the current level.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt 0.5in;TEXT-INDENT:-27pt;tab-stops:.5in;"><SPAN style="FONT-WEIGHT:normal;FONT-FAMILY:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><SPAN style="mso-list:Ignore;"><FONT size=3>·</FONT><SPAN style="FONT:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN></SPAN></SPAN><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">If a procedure calls another procedure on a different server via a linked server, that call should be identified on the call tree.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt 0.5in;TEXT-INDENT:-27pt;tab-stops:.5in;"><SPAN style="FONT-WEIGHT:normal;FONT-FAMILY:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><SPAN style="mso-list:Ignore;"><FONT size=3>·</FONT><SPAN style="FONT:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN></SPAN></SPAN><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">The leaf level identifies the procedures that do not call any other procedure.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">In other words, the procedure call tree should recursively identify all the dependent procedures that are called either directly or indirectly regardless whether they are on the same instance or a different instance.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">I don’t know whether there is any tool out there that would print out such a procedure call tree. But this is not a trivial task if you want to be 100% complete. For instance, if another procedure is called through a piece of dynamic SQL, all bets are off. In that case, what is called will not be known until the run time when the dynamic SQL is executed. That is beyond the reach of any static code analysis.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">Another difficulty is that there is no rigorous T-SQL parser that is publicly available you can use to shred the code of a stored procedure to find all the stored procedure calls. Using a rigorous and efficient T-SQL parser would have been the ideal way to accomplish this task.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">Fortunately, as long as we are not talking about looking for all the dependencies including called procedures, views, tables, and so on, we can manage to get the procedure call tree with a little bit of regular expressions on the fact a stored procedure is always called with an EXECUTE statement. Although there are syntactic variations, it is rather straightforward to single out all the EXECUTE statements in a piece of T-SQL code, and then parse for the stored procedure names being called.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">The attached Perl script does exactly that. If you want to find out what are being called by a procedure, say, p_MyGreatProc in database myDB on server NYSQL, you can run the Perl script as follows:<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';">cmd&gt;perl spCallTree.pl NYSQL.myDB.myOwner.p_Foo &gt; callTree.log<o:p></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">Yes, the script is written to demand a four-part procedure name on the command line on purpose so as to remove any ambiguity, and all the stored procedures in the output are presented in the four-part format for the same reason. <o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">An example of the logged output callTree.log may look like the following:<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';">NYSQL.myDB.myOwner.p_Foo =&gt;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';">{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp; </SPAN>'NYSQL.db1.dbo.p_Foo1' =&gt; <o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>'NYSQL.db2.dbo.p_Foo2' =&gt; <o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="mso-spacerun:yes;">&nbsp;</SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>'PASQL.db1.dbo.p_Foo3' =&gt; <o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>'CASQL.db2.dbo.sp_executesql' =&gt; undef<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>},<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>'NJSQL.db3.dbo.sp_executesql' =&gt; undef<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>},<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>'NYSQL.db.dbo.sp_executesql' =&gt; undef<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>},<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>'NYSQL.db1.dbo.p_Foo4' =&gt; <o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>'NYSQL.db2.dbo.p_Foo5' =&gt; undef,<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;</SPAN>'NJSQL.db2.dbo.p_Foo6' =&gt; undef<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-WEIGHT:normal;FONT-SIZE:11pt;FONT-FAMILY:'Courier New';">};<o:p></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">In this example, p_Foo calls several procedures directly or indirectly across a bunch of servers, including NYSQL, NJSQL, PASQL, and CASQL, and <I style="mso-bidi-font-style:normal;">undef</I> signifies that the procedure does not call any other procedure. So for instance, in the above output, NYSQL.myDB.myOwner.p_Foo calls NYSQL.db1.dbo.p_Foo1, which in turn calls NYSQL.db2.dbo.p_Foo2, which in turn calls PASQL.db1.dbo.p_Foo3, which in turns calls CASQL.db2.dbo.sp_executesql.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">In the case of sp_executesql, more procedures may be called inside the dynamic SQL string executed by sp_executesql. But since we can’t evaluate the string, the script does not look further, and simply points it to undef. In case of EXECUTE on a dynamic SQL string variable, the output will simply identify the variable just in case you want to examine it further. <o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">The Perl script callTree.pl finds out the call tree by scanning the code of the top most procedure for any EXECUTE statement after it has removed all the comments and quoted strings. Well, you would not want to include any reference to EXECUTE mentioned in a comment block, right? For each EXECUTE statement, it parses for the name of the procedure being called, including all the four parts (if necessary). It calls sp_helptext to get the code for the called procedure, and then scans the code for any EXECUTE statement. And this continues recursively until no more procedures are called.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT face="Times New Roman" size=3>I wrote this script fairly quickly as a throw-away script, but have used it many times. I can’t claim that it’s bug free, but so far has worked fine. You do need to have Perl on your machine to run the script. I’ve been using it with Perl v5.10.0, binary release 1003. You can download the latest release of ActivePerl from </FONT><A href="http://www.activestate.com/activeperl/"><FONT face="Times New Roman" color=#606420 size=3>ActiveState</FONT></A><FONT size=3><FONT face="Times New Roman">. <o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">If you need to debug a production issue that involves a procedure makes nested procedure calls with some of the calls going across liked servers, you would appreciate the need to see the complete call tree, and therefore, the utility of this little Perl script.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">I have seen a fair amount of cross-server nested procedure calls, and they are simply nasty. I definitely have better things to do than manually find out which calls which and where. I’m sure you too. So hopefully you’ll find this little script useful.<o:p></o:p></FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><o:p><FONT face="Times New Roman" size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman">And if you have a better or a different way&nbsp;of printing out the complete call tree for a procedure, let me know.</FONT></FONT></SPAN></P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman"></FONT></FONT></SPAN>&nbsp;</P>
<P class=MsoList3 style="MARGIN:0in 0in 0pt;TEXT-INDENT:0in;tab-stops:.5in;mso-list:none;"><SPAN style="FONT-WEIGHT:normal;"><FONT size=3><FONT face="Times New Roman"><o:p></o:p></FONT></FONT></SPAN>&nbsp;</P>