By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

sql server job output file -passing it onto second step

P: n/a
How do we pass on the step1 output file to step2 of the same job? I
would like to know the current job's output file programmetically,
instead of hard coding it. Any idaes would be appreciated.

Thanks

Jul 23 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
It would be helpfull if you can explain what you are doing in each
step. If these steps are calling a stored proc/dts ect... do you know
the output file name ? If so, its easy to pass them as input parameters
to that job. Need more info on what you are trying to acomplish
here....

Jul 23 '05 #2

P: n/a
tram (tr****@hotmail.com) writes:
How do we pass on the step1 output file to step2 of the same job? I
would like to know the current job's output file programmetically,
instead of hard coding it. Any idaes would be appreciated.


It appears to be in msdb.dbo.sysjobsteps.output_file. You could also
call sp_help_jobstep and get the data from there.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

P: n/a
I don't completely understand your question. Here's what I think you
mean: Job1 runs a script (or single query) that generates command
statements. Job2 runs the output from Job1.

One way to do this is use use the command line switches of the isql.exe
(query analyzer). Search for ISQL in books online to see a complete
list of the switches.

use -o to specify an output file.
use -i to specify an input.

Job1's output file would be Job2s input file.

Another way to generate sql, then run it, is to use the sp_executesql
stored procedure. The coding for this can get rather ugly and complex,
if you have to setup cursors. Sometimes isql command line is a simple
quick and dirty solution.

Hope this is what you were for.

Dave

tram wrote:
How do we pass on the step1 output file to step2 of the same job? I
would like to know the current job's output file programmetically,
instead of hard coding it. Any idaes would be appreciated.

Thanks


Jul 23 '05 #4

P: n/a
Thanks for responses. In the first step, I am doing tsql backup
maintenance command. If it fails, in the second step, I am using mail
procedure to send mail to DBAs. My intention is to attach the first
step's output file in the mail so that DBAs could review the log
without logging on to server.

1) I don't want to hard code the file name as it varies from jobn to
job.
2) I need to extract the output file name from one of the jobs tables
for which I need to give the jobID. How to find out the job ID for the
current step?
tr*******@hotmail.com wrote:
I don't completely understand your question. Here's what I think you
mean: Job1 runs a script (or single query) that generates command
statements. Job2 runs the output from Job1.

One way to do this is use use the command line switches of the isql.exe (query analyzer). Search for ISQL in books online to see a complete
list of the switches.

use -o to specify an output file.
use -i to specify an input.

Job1's output file would be Job2s input file.

Another way to generate sql, then run it, is to use the sp_executesql
stored procedure. The coding for this can get rather ugly and complex, if you have to setup cursors. Sometimes isql command line is a simple quick and dirty solution.

Hope this is what you were for.

Dave

tram wrote:
How do we pass on the step1 output file to step2 of the same job? I
would like to know the current job's output file programmetically,
instead of hard coding it. Any idaes would be appreciated.

Thanks


Jul 23 '05 #5

P: n/a
Thanks for responses. In the first step, I am doing tsql backup
maintenance command. If it fails, in the second step, I am using mail
procedure to send mail to DBAs. My intention is to attach the first
step's output file in the mail so that DBAs could review the log
without logging on to server.

1) I don't want to hard code the file name as it varies from jobn to
job.
2) I need to extract the output file name from one of the jobs tables
for which I need to give the jobID. How to find out the job ID for the
current step?
tr*******@hotmail.com wrote:
I don't completely understand your question. Here's what I think you
mean: Job1 runs a script (or single query) that generates command
statements. Job2 runs the output from Job1.

One way to do this is use use the command line switches of the isql.exe (query analyzer). Search for ISQL in books online to see a complete
list of the switches.

use -o to specify an output file.
use -i to specify an input.

Job1's output file would be Job2s input file.

Another way to generate sql, then run it, is to use the sp_executesql
stored procedure. The coding for this can get rather ugly and complex, if you have to setup cursors. Sometimes isql command line is a simple quick and dirty solution.

Hope this is what you were for.

Dave

tram wrote:
How do we pass on the step1 output file to step2 of the same job? I
would like to know the current job's output file programmetically,
instead of hard coding it. Any idaes would be appreciated.

Thanks


Jul 23 '05 #6

P: n/a
tram (tr****@hotmail.com) writes:
Thanks for responses. In the first step, I am doing tsql backup
maintenance command. If it fails, in the second step, I am using mail
procedure to send mail to DBAs. My intention is to attach the first
step's output file in the mail so that DBAs could review the log
without logging on to server.

1) I don't want to hard code the file name as it varies from jobn to
job.
2) I need to extract the output file name from one of the jobs tables
for which I need to give the jobID. How to find out the job ID for the
current step?


Not being a specialist on SQL Server Agent, I looked a little in Books
Online. I found this:

Job steps must be atomic. A job cannot pass Boolean values, data, or
numeric values between job steps. You can pass values from one
Transact-SQL job step to another by using permanent tables or global
temporary tables. You can pass values from one CmdExec job step to
another by using files.

A better approach could be two do all in one step. Now, this may be
problematic with a T-SQL step, since if backup fails, this could be
an error that aborts the batch.

But you could also implement the job as a CmdExec or an ActiveX script.
A CmdExec one is probably the simplest. First it runs the backup command
as

OSQL -n -E -b -u -o logfile.txt -Q "BACKUP ..."

Next you test %ERRORLEVEL% for a non-zero value. If there is a non-zero
value mail the file.

Note here that logfile.txt can be a scratch file. If you also want it
appended to the logfile for the job, just inluce a TYPE of the file in
the job.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7

P: n/a
Erlnad,

Thanks for your constructive reply. I am using db maintenance plan and
in sql sglagent job window I am storing the output in file. In case of
step 1 fails, my aim is to extract the output file name of step 1 and
passit as an attachment to second step.

Jul 23 '05 #8

P: n/a
tram (tr****@hotmail.com) writes:
Thanks for your constructive reply. I am using db maintenance plan and
in sql sglagent job window I am storing the output in file. In case of
step 1 fails, my aim is to extract the output file name of step 1 and
passit as an attachment to second step.


Yeah, I know that by know, but I think you need to give up that path. I
can't think of a way how a T-SQL batch could find which job it's in -
after all the same batch could be running from somewhere else.

Possibly if you run an ActiveX task and use the SQLAgent object, this
information is available.

My idea was to keep it simple.

But you will have to step out of the realms of the maintenance job and
roll your own. Which should not be a big deal.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9

P: n/a
How about this:

Specifiy the output file on the advanced tab of step1. (This way the
filename doesn't change.) Use a third step to make date copy of the
file.

Now you can hard code your failure step (step 2).

Make a step 3 that always fires. Here's sample Dos command line code
to do this.
Rem -----------------------------
Rem -- Parse date and time into xdate and xtime variables
for /F "tokens=2-4 delims=/ " %%a in ('echo %date%') do (set
xdate=%%c%%b%%a)
for /F "tokens=1-2 delims=:,." %%a in ('echo %time%') do (set
xtime=%%a%%b)

set outfile=c:\backup.log
set newfile=backup%xdate%%xtime%.log

rem -- rename the file
ren %outfile% %newfile%
Rem -----------------------------

Dave

tram wrote:
Erlnad,

Thanks for your constructive reply. I am using db maintenance plan and in sql sglagent job window I am storing the output in file. In case of step 1 fails, my aim is to extract the output file name of step 1 and
passit as an attachment to second step.


Jul 23 '05 #10

P: n/a
tram (tr****@hotmail.com) writes:
Thanks for your constructive reply. I am using db maintenance plan and
in sql sglagent job window I am storing the output in file. In case of
step 1 fails, my aim is to extract the output file name of step 1 and
passit as an attachment to second step.


Turns out that I was wrong. Eavesdropping on a discussion between some
of my MVP colleagues, I learnt that SQL Agent has a couple of tokens
that you can pass to a job step, including the job id and the step number.

For a list of available tokens, look in Books Online in the sp_add_jobstep
topic.

Also, see http://www.sqldev.net/sqlagent/SQLAgentStepTokens.htm for some
tips.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.