Joining two tables via ODBC connection

• Marker

  SSChasing Mays

  Points: 602

  More actions

  March 11, 2011 at 3:22 pm

  #248178

  Is it possible to perform a multi-database join via an ODBC connection? I'm attempting to join tables with a non MS database named Pervasive. If so, can someone point me in the right direction on how that gets accomplished? I have one view written in the MS SQL database and another written in the Pervasive SQL database. Both database use T-sql language. Alternatively, can I link the two views via ODBC? I have a index field that I can use to link both.

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  March 13, 2011 at 8:07 am

  #1298111

  Have you tried creating a linked server to the pervasive db from your MS SQL Server db and doing the join using that?

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Nakul Vachhrajani

  SSChampion

  Points: 10294

  More actions

  March 14, 2011 at 1:32 am

  #1298207

  The two most commonly used options are:

  1. OPENROWSET - http://msdn.microsoft.com/en-us/library/ms190312.aspx

  2. Use Linked servers, as Garadin pointed out

  Thanks & Regards,
  Nakul Vachhrajani.
  http://nakulvachhrajani.com

  Follow me on
  Twitter: @sqltwins

• Gosta Munktell

  SSCertifiable

  Points: 6004

  More actions

  March 14, 2011 at 7:38 am

  #1298311

  A little warning.

  You might run into slow respons time. I hope not.

  Google for something like "join linked server slow response".

  In one case I found out it was better to download the table

  to the target server and do the join there.

  Gosta M