multiple data sources, single query

weazy

I have a read-only odbc connection to a sql server database and a full permissions odbc connection to a sql server. I need to query both databases and create a single record-set. Is this possible?

jheitz

What about trying the following?:

1) Create a linked server from one SQL Server to the other one (this is if they are not phisically the same server, you haven´t explained in your email)

2) Create a query joining tables from both databases...

Is this what you are after?

weazy

thanks for the reply. the databases are on the same server ...

is there a way to do it without linking?

jheitz

Why not JOIN the two tables from 2 different databases? As you want a single recordset, this would seem the ideal (and possibly only?) way. If this is not suitable for you, please provide an explanation as well as more details of your database schemas and functionalities so I can understand the issue better

weazy

would it be possible to see sample code doing a join of two databases?

the difficulty I am having with this is that i connect like so:

$conn=odbc_connect("database","UN","PWD");
$conn2=odbc_connect("database1","UN","PWD");

then query like so:

$SQL = "SELECT database.table.listing_id FROM database.table, database2.table2 INNER JOIN database2.table2 ON database2.table2.num = database.table.id"

i then get results like so:
$result = odbc_exec($conn, $sql);

this does not seem to be a workable solution given the way that i get results.

jheitz

It is possible and easy to do it. Establish the connection to ANY of your databases and then do a normal select, similar to what you are doing (your query is syntactically wrong, as you have database2.table2 appearing twice in the FROM section). Try this:

$conn=odbc_connect("database","UN","PWD");
$SQL = "SELECT a.listing_id FROM database.dbo.table a INNER JOIN database2.table2 b ON b.num = a.id"
$result = odbc_exec($conn, $sql);

This should work ok.