SSRS Report: pulling data from two different database servers in SSRS

Question

SSRS Report: pulling data from two different database servers in SSRS

jayendranchellam

SSC Enthusiast

Points: 134
More actions
October 30, 2015 at 2:43 pm

#304646

Please help me to create a report in SSRS for below scenario
I have two different of database server (A & B). I want first server’s table values which is not matched in second Server table
Exp. Server ‘A’ have ‘PPM’ table and Server ‘B’ have ‘SDLC’ table.
SELECT p.PPM_ID, s.PPM_ID
FROM A.PPM p
INNER JOIN B.SDLC s
ON p.PPM_ID = s.PPM_ID
WHERE ISNULL(s.PPM_ID, ‘’) = ‘’

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

To get values that exist in one table that do not exist in another you want to do an anti-join. You can accomplish with using WHERE NOT IN or WHERE NOT EXISTS. My favorite is the EXCEPT set operator:

DECLARE @a TABLE (PPM_ID int);

DECLARE @b-2 TABLE (SDLC int);

INSERT @a VALUES (1),(2),(3),(4);

INSERT @b-2 VALUES (3),(4);

SELECT PPM_ID FROM @a

EXCEPT

SELECT SDLC FROM @b-2;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

jayendranchellam SSC Enthusiast Points: 134 More actions · Answer 2

Thanks for your reply.

But i want the SSRS report, not a SQL query.

Please help me to do above scenario in SSRS.

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

Two datasources, two datasets then join with a lookup between both datasets in the tablix