November 7, 2016 at 8:42 am
hello,
I'm raking my brain and I can't figure out how to do this.
Every morning I review the jobs that fail last night, like any good DBA 😉
Well... anytime a job fails, there is an output written here:
DatabaseBackup_$
(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt
I want to have a way to find out exactly what file is that, so I can open it easily (using notepad++)
I did the following:
select
'\\'+c.server+'\'+
replace(replace(cast(SERVERPROPERTY('errorlogfilename') as varchar(200)),':','$'),'errorlog','')+
'DatabaseBackup_0x'+
cast(a.job_id as varchar(48))+'_'+ --- this is wrong! I need token, not jobID
cast(b.step_id as varchar(2)) +'_'+
cast(c.run_date as varchar(10))+'_'+
cast(c.run_time as varchar(6))
+'.txt'
from msdb..sysjobs a
inner join msdb..sysjobsteps b on b.job_id = a.job_id
inner join msdb..sysjobhistory c on c.job_id = a.job_id
where 1=1
and a.name = 'DatabaseBackup - USER_DATABASES - FULL'
and c.step_name = '(Job outcome)'
and c.run_status <> 1
but the problem is that it gives me the JOB_ID, not the JOBID token.
How do I get the SQL Agent Token from the actual jobID
Thank you a lot!
November 7, 2016 at 9:48 am
I find it easier to log the output of the OH scripts to a table and work from there.
😎
November 7, 2016 at 3:08 pm
Ate you talking about the comnandlog table?
It didn't have enough detail as the log file
November 7, 2016 at 9:37 pm
MiguelSQL (11/7/2016)
Ate you talking about the comnandlog table?It didn't have enough detail as the log file
It has all the backup file names, error code, error number, did you need any other information?
😎
November 8, 2016 at 4:44 am
agent job tokens are detailed at this link
https://msdn.microsoft.com/en-us/library/ms175575.aspx
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply