How do I compare the SQL server agent jobs across 4 servers

Question

0.00/5 (No votes)

See more:

Hello-

I have 4 SQL Server database servers that 'should' store all the same data. They are there for redundancy. Over time, some SQL Server Agent Jobs get added to one server and not the others. The discrepancies build up slowly over time. I am just looking to compare job names across the 4 different servers. I used a FULL OUTER JOIN to include all jobs but the jobs on server B, C and D do not 'line-up' in the query results. The first server (Server A) lines up everything perfectly but if a job doesn't exist on Server A but does on the other 3, the results do not line up for easier reference. I'm not sure how else to do this.

Below are the results I'm getting but do no want. Report 1, 2 and 3 line up great. Report 4 and 5 exists on servers B, C and D but not A. But reports 4 and 5 should be on the same line, except for Server A obviously. Thank you !

SERVER_A	SERVER_B	SERVER_C	SERVER_D
REPORT_1	REPORT_1	REPORT_1	REPORT_1
REPORT_2	REPORT_2	REPORT_2	REPORT_2
REPORT_3	REPORT_3	REPORT_3	REPORT_3
NULL	    REPORT_4	NULL	    NULL
NULL	    REPORT_5	NULL	    NULL
NULL	    NULL	    REPORT_4	NULL
NULL	    NULL	    REPORT_5    NULL
NULL	    NULL	    NULL	    REPORT_4
NULL	    NULL	    NULL	    REPORT_5

What I have tried:

SELECT A.name, B.name, C.name, D.name
FROM A.msdb.dbo.sysjobs A
FULL JOIN B.msdb.dbo.sysjobs B
ON A.name = B.name 
FULL JOIN C.msdb.dbo.sysjobs C
ON A.name = C.name 
FULL JOIN D.msdb.dbo.sysjobs D
ON A.name = D.name

Posted 7-Nov-22 14:52pm

Chris_List

Updated 7-Nov-22 22:02pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2022-11-07T22:02:00

Your joins all depend on A.name, which will be null if the job doesn't exist on server A.

Try something like this:

SQL

SELECT A.name, B.name, C.name, D.name
FROM A.msdb.dbo.sysjobs A
FULL JOIN B.msdb.dbo.sysjobs B ON B.name = A.name
FULL JOIN C.msdb.dbo.sysjobs C ON C.name = Coalesce(A.name, B.name)
FULL JOIN D.msdb.dbo.sysjobs D ON D.name = Coalesce(A.name, B.name, C.name)