I was curious about the associated pros and cons between DTS packages,
SP's and Jobs (executing T-SQL directly). I am using SQL2000.

Basically I prefer building everything into DTS packages, and using the
workflow functionality to encourage parallel processing (Have a quad
and another oct-processor server) so it can really chew through
simultaneous calculations.

I have a co-worker who writes stored procedures, calls them in a dts
package (one sp per task), and then wants that scheduled.
My issues with this is:
Why not just write one stored procedure to call them all, and execute
that one(no variables are being cast, and it's purely sequential
slicing and dicing of data)?
Why not have one SQL task within the DTS to execute them in order
(instead of task1 then upon completion task2, etc)
Also why not just call the stored procedures in the job schedule
directly?

It's pretty simple stuff, but they are claiming stored procedures are
the ultimate. I understand it's a core functionality, but it's a lot of
backtracking when I see a job has failed. (I.E. Check the job, then see
the dts it runs, then check out the SQL tasks and any logging, followed
by opening up the stored procedure listed in each step)

Is it all semantics, any "best" practice?

BOL states:
"SQL Server 2000 and SQL Server version 7.0 incorporate a number of
changes to statement processing that extend many of the performance
benefits of stored procedures to all SQL statements. SQL Server 2000
and SQL Server 7.0 do not save a partially compiled plan for stored
procedures when they are created. A stored procedure is compiled at
execution time, like any other Transact-SQL statement. SQL Server 2000
and SQL Server 7.0 retain execution plans for all SQL statements in the
procedure cache, not just stored procedure execution plans. The
database engine uses an efficient algorithm for comparing new
Transact-SQL statements with the Transact-SQL statements of existing
execution plans. If the database engine determines that a new
Transact-SQL statement matches the Transact-SQL statement of an
existing execution plan, it reuses the plan. This reduces the relative
performance benefit of precompiling stored procedures by extending
execution plan reuse to all SQL statements."

Does this mean stored procedures aren't all they are cracked up to be?

A stored procedure is complied code meaning that SQL Server has already
found the optimal way of executing the SQL code. A stored procedure is
faster the running SQL code which is happening if all the code is placed in
a SQL Execute task.

As far as having all the code in one stored procedure: If you have the
code broken up to small stored procedures then the code is easier to debug
and have some else look at it and not get confused by the sheer length of
the sp.

Putting all the code in 1 SQL Execute task makes it hard to debug. When it
is scheduled to run and if it fails you know which steps fails and it is
much easier to track do what step actually failed instead of knowing all the
sp's failed.

Rick

"Mnemonic" <MICHAEL_SUNLIN (AT) COUNTRYWIDE (DOT) COM> wrote

I could also argue that having the code inside one large bulk of code
would be easier to debug because you don't have to check environmental
states (tables being created/dropped between the various Sp). With the
proper documentation, nothing can get too big to digest.

These stored procedures are also not "re-usable". They are just one
shot code that may run once or day or once a month. The developer sites
the pre-compiled execution plan is the reason for doing it this way.

But the Books on line passage I quoted above "SQL Server 2000
and SQL Server 7.0 retain execution plans for all SQL statements in the

procedure cache, not just stored procedure execution plans"
Does this mean stored procedures are still not the only code that
benefits from execution plan retention? Or am I misreading this?