Linked Server Subquery/Left Outer Join Issue

Question

Linked Server Subquery/Left Outer Join Issue

Steve Hanzelman

Valued Member

Points: 50
More actions
August 14, 2009 at 11:48 am

#209597

I have a simple script that works on a server (ServerA). When I run that same script on another server (ServerB) that is linked to ServerA, it throws an error stating that std.personid, stucon.personid, std.calendarid, stucon.calendarid are all 'invalid columns'.
If I use a subquery to pull the information rather than a left outer join, it gives the same messages.
All of them work perfectly fine on ServerA. Ideas?
SELECT
DISTINCT 'abc' as theUser
,getDate() as aDate
,std.studentNumber+SPACE(12) as studNumber
,left(stucon.lastname,20) as homeName
FROM ServerA.x.dbo.sif_studentSnapshot std left outer joinServerA.x.dbo.sif_studentContact stucon
on std.personid = stucon.personid and stucon.relationship = 'Home' and std.endYear = stucon.endYear
and std.calendarid = stucon.calendarid
where std.schoolNumber <> 6 and std.endYear = (SELECT endYear FROM ServerA.x.dbo.SchoolYear WHERE active = 'True')
and std.secondaryHM = 0

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

I don't know about the error message because as a general principle I NEVER join tables across servers. Even when it works, the performance just crawls. Do yourself a favor and rewrite this as a stored procedure or two. Push your select data across into a temporary table, then call the stored procedure that does the join, so that it all happens on one machine.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Steve Hanzelman Valued Member Points: 50 More actions · Answer 2

Bob,

Thanks for the reply.

I was in the process of rewriting it just that way when I saw your response. I guess that is the way I'll be going.

Thanks,

Steve

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

It's way better in terms of performance. You won't be sorry.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills