August 18, 2014 at 8:45 am
Hi,
I have the following select statement which I am using to retrieve job history:
select name, step_name, run_date, run_time, run_duration, run_status
from msdb.dbo.sysjobhistory h
inner join msdb.dbo.sysjobs j on h.job_id = j.job_id
where name = 'DBBackups.Subplan_1'
order by run_date
I am doing this join so I can get the actual name of the job.
When I run this select statement in SSMS it correctly shows all of my columns.
However when I add this into a job and then do an export, in my export file I can only ever see the very first column from my select statement which is 'name' in this case).
I have tried changing my join to an inner, left outer and right outer but nothing seems to work.
I'm not sure why the first column is only being exported to my .txt file and nothing else.
Any help most appreciated.
August 18, 2014 at 8:51 am
You need to be a lot more detailed about exactly what steps you took. You said you added the query to a job, but failed to state exactly how, and/or with what options, or what type of job, and such. Explicit detail is necessary...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 18, 2014 at 8:53 am
how you are generating your file is the cause not the query if its giving you the proper result.
check if you are able to pass all the columns which you want to export to the file generator or not.
August 19, 2014 at 2:23 am
Apologies or being brief. Yes it was the file I was exporting to (.txt file) which wasn't showing all of my columns.
I have exported it to a .csv file and it is showing all of my columns.
The only problem I am having is that the data is not neatly aligned under each column.
Any idea how to format the data in a CSV file? I have tried adding commas in my select statement like this but it hasn't worked:
Declare @SQL varchar(500)
set @SQL = 'select name,' + ',' + ' step_name, ' + ',' + ' run_date, run_time, run_duration, run_status
from msdb.dbo.sysjobhistory h
inner join msdb.dbo.sysjobs j on h.job_id = j.job_id
where name = "DBBackups.Subplan_1"
order by run_date'
Print @SQL
Thanks.
August 19, 2014 at 2:26 am
zedtec (8/19/2014)
Apologies or being brief. Yes it was the file I was exporting to (.txt file) which wasn't showing all of my columns.I have exported it to a .csv file and it is showing all of my columns.
The only problem I am having is that the data is not neatly aligned under each column.
Any idea how to format the data in a CSV file?.
CSV files are not really intended to be formatted. You can get some results by padding with spaces, then making sure the app you use to view the CSV file uses a fixed pitch font, but it's a hokey kludge. Better to import into something like Excel and use its formatting abilities.
August 19, 2014 at 3:49 am
There are many possible ways to generate CSV file.
1) Use BCP command line tool to generate CSV
2) Use SQL SERVER Import/Export wizard to generate SSIS Package and use that.
3) Use a front end application to generate CSV.
Depends how you wants it. if you are interested in query then here it is:
select name +',' + step_name +',' + Cast(run_date as varchar(10))+',' + Cast(run_time as varchar(10)) +',' + Cast(run_duration as varchar(10)) +',' + Cast(run_status as varchar(20))
from msdb.dbo.sysjobhistory h
inner join msdb.dbo.sysjobs j on h.job_id = j.job_id
where name = 'DBBackups.Subplan_1'
order by run_date
Hope it helps.
August 19, 2014 at 7:57 am
That's great thanks for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply