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?

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  April 10, 2008 at 2:50 pm

  #801197

  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

  April 10, 2008 at 3:14 pm

  #801207

  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

  April 10, 2008 at 3:27 pm

  #801211

  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

  April 10, 2008 at 3:31 pm

  #801215

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