July 16, 2011 at 9:10 am
Hello,
I need to create a Report to show the jobs and history on various servers.
I found the code to run which tells me about the job history.
Now I want to run this script on each server.
1) Add ForEACH loop container onto control flow
2) Add script task inside of loop container
3) Run the query
4) Save the resultset and insert it into a table
5) Go to the next in the ForEach loop.
How do I run the query in the script task and then save the resultset and then insert it into a table?
Thanks,
Tony
P.S.
The following is the code I found at Jamesserra.com that does what I need. I need to run this on each of the servers that are in my control table. I modified it for what I need.
SELECT sysjobhistory.server,
sysjobs.name
AS
job_name,
CASE sysjobhistory.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
ELSE '???'
END
AS
run_status,
Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +
Substring(CONVERT(VARCHAR
(8), run_date), 5, 2) + '-' +
Substring(CONVERT(VARCHAR(
8), run_date), 7, 2), '')
AS
[Run DATE],
Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'
+
Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2
)
+
':' +
Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '')
AS
[Run TIME],
Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +
':' +
Substring(CONVERT(VARCHAR(7), run_duration+1000000),
4,
2)
+ ':' +
Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),
''
) AS
[Duration],
sysjobhistory.step_id,
sysjobhistory.step_name,
sysjobhistory.MESSAGE
FROM msdb.dbo.sysjobhistory
INNER JOIN msdb.dbo.sysjobs
ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
Things will work out. Get back up, change some parameters and recode.
July 16, 2011 at 3:34 pm
Is this the same issue as this or am I missing the nuance?
http://www.sqlservercentral.com/Forums/Topic1142962-391-1.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 16, 2011 at 3:43 pm
This is a duplicate. I didn't even know that I had the submit button.
I would delete it if I knew how.
Sorry for the inconvenience.
Things will work out. Get back up, change some parameters and recode.
July 16, 2011 at 3:47 pm
No worries. Please refer all comments to the other thread going forward. I think the comment I made on the other thread will help you solve the issue without using a Script Task. Think of Script Tasks and Script Components as "glue" only to be used as a last resort when no other built-in options will work.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply