Hello and thanks to all of the helpers!
Especially for Linchi Shea for the Perl script.
When I compare the sql script created by the perl script (after little
modification on my side) with the one created by the MS GUI tool for
creating SQL scripts, I get the stored procedues on different order. When
running the script from SQL Query Analyzer, it displays errors (warnings?):
"Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'GUIRemoveEntity'. The stored procedure will
still be created."
I understand that the order of them DOES matter, but, if at the end of the
process, all of the SPs are loaded, will everything work?
Thanks!
RoeeIf the script is atomic (e.g. drops and re-creates the stored procedures),
running the script a second time should solve the problem.
sysdepends is generally unreliable anyway, and I typically ignore this
message unless I heavily rely on the feature and religiously make sure it is
correct.
A
"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in message
news:bo5mfh$182orj$1@.ID-200860.news.uni-berlin.de...
> Hello and thanks to all of the helpers!
> Especially for Linchi Shea for the Perl script.
> When I compare the sql script created by the perl script (after little
> modification on my side) with the one created by the MS GUI tool for
> creating SQL scripts, I get the stored procedues on different order. When
> running the script from SQL Query Analyzer, it displays errors
(warnings?):
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'GUIRemoveEntity'. The stored procedure will
> still be created."
> I understand that the order of them DOES matter, but, if at the end of the
> process, all of the SPs are loaded, will everything work?
> Thanks!
> Roee
>|||Just a word of caution. if you run the script twice with
DROP PROC placed immediately before its respective CREATE
PROC, you won't see the error message. But it doesn't
solve the problem of dependency.
The best you can do with sysdepends is not to use it.
The only way to be sure of the dependency is to scan the
TSQL code and sort the depenency yourself. If you see
anybody claiming that his tool correctly handles the
procedure dependency, ask how he determines the
dependency. If it doesn't parse the code, don't believe it.
In addition, if someone claims that he has a pure TSQL
script that can sort the SP dependency correctly, don't
believe it either.
Rosee, I do have a Perl script that scan the SP code and
sort them by their dependency. Drop me an email if you are
interested.
Linchi
>--Original Message--
>If the script is atomic (e.g. drops and re-creates the
stored procedures),
>running the script a second time should solve the problem.
>sysdepends is generally unreliable anyway, and I
typically ignore this
>message unless I heavily rely on the feature and
religiously make sure it is
>correct.
>A
>
>"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in
message
>news:bo5mfh$182orj$1@.ID-200860.news.uni-berlin.de...
>> Hello and thanks to all of the helpers!
>> Especially for Linchi Shea for the Perl script.
>> When I compare the sql script created by the perl
script (after little
>> modification on my side) with the one created by the MS
GUI tool for
>> creating SQL scripts, I get the stored procedues on
different order. When
>> running the script from SQL Query Analyzer, it displays
errors
>(warnings?):
>> "Cannot add rows to sysdepends for the current stored
procedure because it
>> depends on the missing object 'GUIRemoveEntity'. The
stored procedure will
>> still be created."
>> I understand that the order of them DOES matter, but,
if at the end of the
>> process, all of the SPs are loaded, will everything
work?
>> Thanks!
>> Roee
>>
>
>.
>|||> The only way to be sure of the dependency is to scan the
> TSQL code and sort the depenency yourself.
Agreed.
> Rosee, I do have a Perl script that scan the SP code and
> sort them by their dependency.
I'm sure this could be done in T-SQL as well... maybe an interesting
exercise if I get bored. :-)|||Aaron;
Do you want to bet two beers at PASS2003 (if you are
going) that if a robust SP dependency sort program is done
in TSQL, it would be so ugly that it's practically
undoable? Ugliness is of course a matter of opinion and
lies the eyes of the the beholder :-)
I have no doubt you can do it in TSQL or any other
language that supports varibles, some string functions,
and the basic set of control flow constructs.
One of the major problems of doing this in TSQL is that
you need true recursion, which you can simulate with TSQL,
but rather unsightly. By 'robust', I mean it has to deal
with all the nuances.
Linchi
>--Original Message--
>> The only way to be sure of the dependency is to scan the
>> TSQL code and sort the depenency yourself.
>Agreed.
>> Rosee, I do have a Perl script that scan the SP code and
>> sort them by their dependency.
>I'm sure this could be done in T-SQL as well... maybe an
interesting
>exercise if I get bored. :-)
>
>.
>
sql
