How do I query a Linked Server

Question

How do I query a Linked Server

james2003_

Grasshopper

Points: 22
More actions
April 10, 2008 at 2:45 pm

#181291

Hi,
I'm using MSSQL Server 2005
I have a database named liteEval in the server that has the
tabel refConcUnits. I have a paradox table named "concentration_units".
I go into management studio and create a linked server to the paradox table "PDXCOMMON". I can run queries off this fine if I do the following:
SELECT code,description
FROM OPENQUERY(pdxcommon, 'SELECT * FROM concentration_units')
The problem is, I need to link this table to the MSSQL table in Lite Eval named refConcUnits on code = strunits.
I cannot figure out the syntex. Can someone please help?

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Not being familiar with paradox I can't be sure this will work, but SQL Server allows for 4 part naming in LInked server queries like this:

Select

Column List

From

LinkedServer.Database.Schema.Table

For paradox you may need to eliminate the word schema and just use "LinkedServer.Database..TAble"

Another option is to use your current query to insert into a table variable or temp table and join on it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

or put your query in a derived table:

select *

from refConcUnits r

join (SELECT code,description FROM OPENQUERY(pdxcommon, 'SELECT * FROM concentration_units')) x

on x.code=r.strunits

EDIT: forgot to mention that cross server joins will kill your perf. so it may be better to dump the results of the linked server call to a temp table and join to that as the previous post suggests.

---------------------------------------
elsasoft.org

Kangana Beri SSCrazy Points: 2184 More actions · Answer 3

I would also suggest using table variable or temp table here, you can do:

SELECT code, description INTO temp_table FROM OPENQUERY(PRODJDE,' pdxcommon, 'SELECT * FROM concentration_units')

And then use this temp_table as:

select *

from refConcUnits r

join temp_table t

on x.code = t.strunits

Finally

DROP TABLE temp_table

james2003_ Grasshopper Points: 22 More actions · Answer 4

Thanks for your input. I was able to get Old Hands solution to work:)