sql server job output file -passing it onto second step

[tram]

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 programmeticall y,
instead of hard coding it. Any idaes would be appreciated.

Thanks

[SQLDBA]

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....

[Erland Sommarskog]

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 programmeticall y,
instead of hard coding it. Any idaes would be appreciated.

It appears to be in msdb.dbo.sysjob steps.output_fi le. You could also
call sp_help_jobstep and get the data from there.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[treschaud]

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 programmeticall y,
instead of hard coding it. Any idaes would be appreciated.

Thanks

[tram]

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*******@hotma il.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 programmeticall y,
instead of hard coding it. Any idaes would be appreciated.

Thanks

[tram]

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*******@hotma il.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 programmeticall y,
instead of hard coding it. Any idaes would be appreciated.

Thanks

[Erland Sommarskog]

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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[tram]

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.

[Erland Sommarskog]

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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dave]

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:\back up.log
set newfile=backup% xdate%%xtime%.l og

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.