Good SQL Server Agent book?

I plan to automate a process using about 20 steps and would
like to use SQL Server Agent. I'm finding it difficult to learn how
Agent works. The help files are skimpy and scattered and
leave many questions unanswered.
One thing I've noticed is that Agent does a lot of things behind
the curtain that the user did not request. If I write a script using
TSQL commands like sp_add_job, sp_add_job_step, etc., and
then invoke it via Agent, Agent does things that aren't in my
script. If I ask Agent to generate a script for my job it spits
out a script that includes most of what I ordered, but with
a lot of other boilerplate that I didn't order and some things
like transaction management, that may not have been what
I wanted.
Is all of this documented somewhere? Has someone written
a good book that covers this and gives recommendations on
the best ways to use Agent?
Thanks.
Alan<ameyer2@.yahoo.com> wrote in message
news:1153251901.175708.34630@.s13g2000cwa.googlegroups.com...
>I plan to automate a process using about 20 steps and would
> like to use SQL Server Agent. I'm finding it difficult to learn how
> Agent works. The help files are skimpy and scattered and
> leave many questions unanswered.
> One thing I've noticed is that Agent does a lot of things behind
> the curtain that the user did not request. If I write a script using
> TSQL commands like sp_add_job, sp_add_job_step, etc., and
> then invoke it via Agent, Agent does things that aren't in my
> script. If I ask Agent to generate a script for my job it spits
> out a script that includes most of what I ordered, but with
> a lot of other boilerplate that I didn't order and some things
> like transaction management, that may not have been what
> I wanted.
> Is all of this documented somewhere? Has someone written
> a good book that covers this and gives recommendations on
> the best ways to use Agent?
>
I would build your 20-step process in an SSIS package. Then deploy that
package as a 1-step SQL Agent job.
SSIS has a graphical workflow designer, integrated debugger, rich logging,
etc.
David|||David Browne wrote:
> ...
> I would build your 20-step process in an SSIS package. Then deploy that
> package as a 1-step SQL Agent job.
> SSIS has a graphical workflow designer, integrated debugger, rich logging,
> etc.
> David
Thanks David.
Unfortunately, we're still running SQLServer 2000, and it looks
like we'd have to upgrade our servers to SQL Server 2005 to
use SSIS. That may be more expensive and time consuming
than management would support just for this task.
Agent also has a graphical user interface, though I'm not sure
I wouldn't prefer submitting a script. The big advantage of a
script over a GUI, in my view, is that you can see everything
that happens in one listing, and if you're running multiple servers
you can easily move it to all of them. You can also document
a script with extensive comments.
However it looks like Microsoft really doesn't want you using
scripts for this purpose, so we'll probably use the GUI and have
it generate a script for us to load on multiple servers and to
store in our documentation safe.
Regards,
Alan|||Its not really documented but you can get some guidance by click on help in
the job help dialog. Right click on your job and select help is a
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<ameyer2@.yahoo.com> wrote in message
news:1153261730.359715.126050@.i42g2000cwa.googlegroups.com...
> David Browne wrote:
> Thanks David.
> Unfortunately, we're still running SQLServer 2000, and it looks
> like we'd have to upgrade our servers to SQL Server 2005 to
> use SSIS. That may be more expensive and time consuming
> than management would support just for this task.
> Agent also has a graphical user interface, though I'm not sure
> I wouldn't prefer submitting a script. The big advantage of a
> script over a GUI, in my view, is that you can see everything
> that happens in one listing, and if you're running multiple servers
> you can easily move it to all of them. You can also document
> a script with extensive comments.
> However it looks like Microsoft really doesn't want you using
> scripts for this purpose, so we'll probably use the GUI and have
> it generate a script for us to load on multiple servers and to
> store in our documentation safe.
> Regards,
> Alan
>|||Hilary Cotter wrote:
> Its not really documented but you can get some guidance by click on help i
n
> the job help dialog. Right click on your job and select help is a
> ...
Thanks Hilary.
I also just found this:
http://www.microsoft.com/technet/pr...s/c12ppcsq.mspx
It seems to give more information than is in the help file.
Alan|||ameyer2@.yahoo.com wrote:
> David Browne wrote:
>
> Thanks David.
> Unfortunately, we're still running SQLServer 2000, and it looks
> like we'd have to upgrade our servers to SQL Server 2005 to
> use SSIS. That may be more expensive and time consuming
> than management would support just for this task.
> Agent also has a graphical user interface, though I'm not sure
> I wouldn't prefer submitting a script. The big advantage of a
> script over a GUI, in my view, is that you can see everything
> that happens in one listing, and if you're running multiple servers
> you can easily move it to all of them. You can also document
> a script with extensive comments.
> However it looks like Microsoft really doesn't want you using
> scripts for this purpose, so we'll probably use the GUI and have
> it generate a script for us to load on multiple servers and to
> store in our documentation safe.
> Regards,
> Alan
>
Hi Alan
Why don't you just create a DTS package and then run this package from
SQL server agent?
I must admit that I'm not quite sure what your problem is because I've
used SQL agent for years for many different purposes and I haven't
really seen that it add things I haven't asked for.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Hello Steen. Thank you for the reply
Steen Persson (DK) wrote:

> Why don't you just create a DTS package and then run this package from
> SQL server agent?
Thanks for the suggestion. I will look into that.

> I must admit that I'm not quite sure what your problem is because I've
> used SQL agent for years for many different purposes and I haven't
> really seen that it add things I haven't asked for.
I presume you have mainly used the Agent GUI.
To see what I'm talking about, pick one of your Agent jobs
and export it as a text script, e.g.,
Open Agent.
Select any job
Right click on the job and select "All Tasks" / "Generate SQL
Script..."
Then examine what you get.
If you write your own script and submit it to SQL Server without
using the GUI front end, you can get your new job to show up in
the GUI list of jobs. However if you then use the GUI to generate
a script from it and compare what was generated to what you
input, you might be surprised. I was.
Alan|||ameyer2@.yahoo.com wrote:
> Hello Steen. Thank you for the reply
> Steen Persson (DK) wrote:
>
> Thanks for the suggestion. I will look into that.
>
> I presume you have mainly used the Agent GUI.
> To see what I'm talking about, pick one of your Agent jobs
> and export it as a text script, e.g.,
> Open Agent.
> Select any job
> Right click on the job and select "All Tasks" / "Generate SQL
> Script..."
> Then examine what you get.
> If you write your own script and submit it to SQL Server without
> using the GUI front end, you can get your new job to show up in
> the GUI list of jobs. However if you then use the GUI to generate
> a script from it and compare what was generated to what you
> input, you might be surprised. I was.
> Alan
>
Hi Alan
I still don't quite understand what your issue is. It's true that if I
generate a sql script from an existing job, there are more code in it,
but there's nothing I don't expect to be there. There are number of
checks if the job already exist etc. and that's all right for me. If you
don't check for this in your "manual" script you'll get an error if you
try to add a job that already exists so why shouldn't the check be there
when you generate the script?
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||ameyer2@.yahoo.com wrote:
> Hello Steen. Thank you for the reply
> Steen Persson (DK) wrote:
>
> Thanks for the suggestion. I will look into that.
>
> I presume you have mainly used the Agent GUI.
> To see what I'm talking about, pick one of your Agent jobs
> and export it as a text script, e.g.,
> Open Agent.
> Select any job
> Right click on the job and select "All Tasks" / "Generate SQL
> Script..."
> Then examine what you get.
> If you write your own script and submit it to SQL Server without
> using the GUI front end, you can get your new job to show up in
> the GUI list of jobs. However if you then use the GUI to generate
> a script from it and compare what was generated to what you
> input, you might be surprised. I was.
> Alan
>
Perhaps you should analyze that additional code to determine what it's
doing? Maybe you're missing a step in your own scripts?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:

> Perhaps you should analyze that additional code to determine what it's
> doing? Maybe you're missing a step in your own scripts?
>
I have indeed analyzed it. Some of what is there is stuff that either
I don't need because I've done the same thing another way, or
because I just don't need it.
Some is questionable. I'm not in front of SQL Server right now,
but as I recall, it wraps my entire job step in a transaction. That
may be right in the average case but wrong in particular cases
where it could defeat the intent of the programmer.
I can see why this approach by Microsoft has its fans. I'm
just not one of them.
But I didn't create this thread for the purpose of flaming
Microsoft. I recognize the MS has produced many fine
programs, SQL Server among them. I just wanted to know
if anyone knew of any documentation of the things MS does
under the covers of the Agent GUI.
I did find one source of information. See my first posting on
July 19 for the reference. If anyone knows of any other
documentation I'd love to see it.
Thanks.
Alan