Jeff Moden (8/30/2012)
It's been several years since I've had to work with OPENQUERY but I believe the following will work...
select <stuff ...>
from OPENQUERY(Banner_Prod, 'SELECT a.Major
,a.FIRSTNAME AS NameFirst
,a.MIDDLENAME AS NameMiddle
,a.LASTNAME AS NameLast
,a.CLASSCODE AS PersonSubType
FROM fredtrailer.student_Directory_info a'
) oq
WHERE oq.STUD_ID = @STUD_ID
The only thing that I don't remember is whether or not the external criteria is reflected in the OPENQUERY or if the whole OPENQUERY result set is returned before the criteria is applied. The performance test for that is, of course, simple enough. Give it a try.
Thank you Jeff. This is great to read. I have searched but every solution comes with dynamic sql and I wanted to avoid it 🙂
Can we pass Linked Server Name as variable without dynamic sql ?