help with OPENQUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help with OPENQUERY

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2012-07-11 : 13:13:30

I was given an impossible task and i'm hoping someone is good with OPENQUERY synatx and dynamic sql because it is not my strong point.
Basically, down below where the first comment starts as --start openquery, I need to pass that entire query into an OPENQUERY which should not be that hard except there are so many quotations and other stuff in addition to sub queries and CTEs. They basically want to run that report below by selecting a different server in the openquery. Any help would be greatly appreciated.

Thanks,

SET NOCOUNT ON;
DECLARE @xp_results TABLE (
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL);

DECLARE @job_owner AS sysname;
DECLARE @HistoryRange AS INT
SET @HistoryRange = -1

SET @job_owner = SUSER_SNAME();

INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner;

---start openquery

select * from openquery

(serverName,

;WITH CTESteps
AS (SELECT Isjs.job_id,
Isjs.step_name,
isjh.step_id AS RunningStep_id,
Isjh.run_status,
MAX(Isjs.Step_id) OVER (PARTITION BY Isjs.job_id) AS MaxStepID,
MAX(Isjh.Step_id) OVER (PARTITION BY Isjs.job_id) AS
MaxRunningStepID,
Isjh.[message],
MIN(CAST (STR(Isjh.run_date) AS DATETIME) + CAST
(STUFF(STUFF(REPLACE(STR(Isjh.run_time, 6, 0), ' ', '0'), 3, 0, ':'),
6, 0, ':') AS TIME)) OVER (PARTITION BY Isjs.job_id) AS
Job_Start_DateTime,
MAX(CAST (STR(Isjh.run_date) AS DATETIME) + CAST
(STUFF(STUFF(REPLACE(STR(Isjh.run_time, 6, 0), ' ', '0'), 3, 0, ':'),
6, 0, ':') AS TIME)) OVER (PARTITION BY Isjs.job_id) AS
Job_End_DateTime,
CAST(CAST (STUFF(STUFF(REPLACE(STR(Isjh.run_duration %
240000, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS TIME)
AS Step_Duration,
Isjh.run_duration / 240000 AS ExtraDays
FROM (SELECT *
FROM msdb..sysjobsteps WITH (NOLOCK)
) AS Isjs
INNER JOIN
(SELECT *
FROM msdb..sysjobhistory s WITH (NOLOCK)
WHERE run_date >= (select MAX(run_date) from
msdb..sysjobhistory t (nolock) where t.run_date >=
CONVERT(varchar(8),Dateadd(DD,@HistoryRange,getdate()),112) and
t.job_id = s.job_id and t.step_id = 1)
AND run_time >= (select MAX(run_time) from msdb..sysjobhistory
t (nolock) where t.run_date = s.run_date and t.job_id = s.job_id and
t.step_id = 1)
) AS Isjh
ON Isjs.step_id = Isjh.step_id AND Isjs.job_id = Isjh.job_id
)

,CTEJobs AS(
SELECT A.job_id,
MAX(Job_Start_DateTime) AS Job_Start_DateTime,
MAX(CASE
WHEN RunningStep_id = MaxRunningStepID THEN CAST ((DATEDIFF(DD,
Job_Start_DateTime, (Job_End_DateTime + Step_Duration) - CAST
(Job_Start_DateTime AS TIME)) + ExtraDays) AS VARCHAR (10)) + ' Days '
+ CAST (CAST ((Job_End_DateTime + Step_Duration) - CAST
(Job_Start_DateTime AS TIME) AS TIME) AS VARCHAR (8))
END) AS Job_Duration,
MIN(CASE
WHEN MaxStepID = MaxRunningStepID AND RunningStep_id =
MaxRunningStepID AND run_status IN (1, 4) THEN ' SUCCESS'
WHEN MaxStepID != MaxRunningStepID AND RunningStep_id =
MaxRunningStepID AND run_status IN (1, 4) THEN 'IN PROGRESS'
WHEN RunningStep_id = MaxRunningStepID AND run_status IN (0, 3)
THEN 'FAILED'
WHEN RunningStep_id = MaxRunningStepID AND run_status = 2 THEN 'RETRYING'
END) AS Job_Status,
'Last Running Step Status: [ ' + MIN(CASE
WHEN MaxStepID = MaxRunningStepID AND RunningStep_id =
MaxRunningStepID AND run_status IN (1, 4) THEN ' SUCCESS'
WHEN MaxStepID != MaxRunningStepID AND RunningStep_id =
MaxRunningStepID AND run_status IN (1, 4) THEN 'IN PROGRESS'
WHEN RunningStep_id = MaxRunningStepID AND run_status IN (0,
3) THEN 'FAILED'
WHEN RunningStep_id = MaxRunningStepID AND run_status = 2 THEN
'RETRYING'
END
+ ' ] ||'+CHAR(13)+'Last Running Step#:'
+ CASE
WHEN RunningStep_id = MaxRunningStepID THEN
STR(MaxRunningStepID) + ' (' + step_name + ') ||'+CHAR(13) +
ISNULL([message], '')
END )AS Last_Step_Message
FROM CTESteps A
GROUP BY A.job_id
)

SELECT
Job_Name,
ISNULL(STUFF(STUFF(STUFF(CONVERT(varchar(50),
Job_Start_DateTime,109),21,4,' '),1,4,CAST(MONTH(Job_Start_DateTime)
AS VARCHAR(2)) + '/'), LEN(MONTH(Job_Start_DateTime)) + 4,1,'/'),'')
AS Job_Start_DateTime,
ISNULL(CASE WHEN Job_Status IN('IN PROGRESS','RETRYING')
THEN CAST (DATEDIFF(DD, Job_Start_DateTime, (GETDATE()) - CAST
(Job_Start_DateTime AS TIME)) AS VARCHAR (10)) + ' Days ' + CAST (CAST
((GETDATE()) - CAST (Job_Start_DateTime AS TIME) AS TIME) AS VARCHAR
(8))
ELSE Job_Duration END,'') AS Job_Duration,
Job_Status,
CASE WHEN Job_Start_DateTime IS NULL THEN 'Job is not Running.'
WHEN Job_Status = 'IN PROGRESS' THEN 'Job is in Progress.'
WHEN Job_Status = 'RETRYING' THEN 'Job is in between Retries.'
ELSE LEFT(last_outcome_message,
charindex('.',last_outcome_message,1)) END AS Job_Message,
CASE WHEN Job_Status = 'SUCCESS' THEN last_outcome_message
ELSE ISNULL(Last_Step_Message,'') END AS Last_Step_Message
FROM
(
SELECT J.name AS Job_Name,
A.Job_Start_DateTime,
A.Job_Duration,
CASE WHEN A.Job_Status NOT IN('IN PROGRESS', 'RETRYING') AND
C.last_run_outcome = 1 THEN 'SUCCESS'
WHEN A.Job_Status NOT IN('IN PROGRESS', 'RETRYING') AND
C.last_run_outcome = 0 THEN 'FAILED'
WHEN A.Job_Status NOT IN('IN PROGRESS', 'RETRYING') AND
C.last_run_outcome = 3 THEN 'CANCELED'
WHEN A.Job_Status = 'IN PROGRESS' AND B.running IN (1, 2, 7) THEN
'IN PROGRESS'
WHEN A.Job_Status = 'IN PROGRESS' AND B.running = 3 THEN 'RETRYING'
WHEN A.Job_Status = 'IN PROGRESS' AND B.running NOT IN (1,2,7,3)
THEN 'SUCCESS'
ELSE ISNULL(A.Job_Status,'IDLE')
END AS Job_Status,
C.last_outcome_message,
A.Last_Step_Message
FROM CTEJobs AS A
INNER JOIN
msdb..sysjobs AS J ON A.job_id = J.job_id
LEFT OUTER JOIN
@xp_results AS B ON A.job_id = B.job_id
LEFT OUTER JOIN
msdb.dbo.sysjobservers C ON A.job_id = C.job_id
) JOBs
ORDER BY Job_Status

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-11 : 13:44:19

Invert your code: take all the expressions out of the OPENQUERY call and just SELECT columns. Move the expressions to the outer SELECT. In general, you do not want to perform JOINs or lots of crazy logic with OPENQUERY or 4-part queries. Grab the remote data, put it into a local temp table, and work with it from there. If you have multiple servers this is even more important.

There are tons of other comments/questions I have but see if this makes sense first. I'm not even sure I see any reason for dynamic SQL at all, unless you have a varying number of servers to check.