| Groups | Blog | Home |
sql server (alternate) : sql server job output file -passing it onto second step
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
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....
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 [quoted text, click to view] 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
tram (tram_e@hotmail.com) writes:
[quoted text, click to view] > 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, esquel@sommarskog.se Books Online for SQL Server SP3 at
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? [quoted text, click to view] treschaud@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
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? [quoted text, click to view] treschaud@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
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.
tram (tram_e@hotmail.com) writes:
[quoted text, click to view] > 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, esquel@sommarskog.se Books Online for SQL Server SP3 at
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 [quoted text, click to view] 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.
tram (tram_e@hotmail.com) writes:
[quoted text, click to view] > 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, esquel@sommarskog.se Books Online for SQL Server SP3 at
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |