Query using multiple databases?

[Gary1]

I also need help in joining tables from multiple databases. Using CF5. The
last post here says to use dot-dot notation. But that doesn't work for me for
several reasons. First, I have both databases configured in CF Administrator.
One is on the same server using SQL Server 2000 (OLEDB configured). The other
database is an IBM UDB/DB2 database which uses an ODBC client driver installed
on the main CF server.

But the real problem is with the <CFQUERY> tag. You have to put a Datasource
or DBType. You can only list ONE datasource or ONE DBType. I need to join
tables from TWO datasources and TWO DBTypes. So, even if you use the dot-dot
notation, the CFQUERY returns an error that it cannot find the "other" table,
depending on which datasouce you have indicated in the CFQUERY tag.

Is this even possible? Is there any way to do this join? I've tried using
QofQ, but I need to do a LEFT OUTER JOIN, so that all records in one table are
displayed, even though there are fewer matching records in the other table.
You can't do a LEFT OUTER JOIN with CF5's QofQ, right?

I thought about running 2 queries, then outputing the results to LISTS. Then,
use CFLOOP to loop through the lists, and using REPLACELIST to match/join the
data based on the matching items in the lists. Not sure if even this will
work. Just wondering if it's even possible to join 2 tables from 2 different
databses, when the DBTypes and Datasource types are both different?

Thanks for any help/advice.
Gary.

[jorgepino]

if you admin rigths to both databases
you should just created a odbc conection within MSsql between both database
then you should be able to call both database by only using one source data
base

easy
Jorge

[Neculai Macarie]

> I also need help in joining tables from multiple databases. Using CF5.
The

> last post here says to use dot-dot notation. But that doesn't work for me

for

> several reasons. First, I have both databases configured in CF

Administrator.

> One is on the same server using SQL Server 2000 (OLEDB configured). The

other

> database is an IBM UDB/DB2 database which uses an ODBC client driver

installed

> on the main CF server.
>
> But the real problem is with the <CFQUERY> tag. You have to put a

Datasource

> or DBType. You can only list ONE datasource or ONE DBType. I need to

join

> tables from TWO datasources and TWO DBTypes. So, even if you use the

dot-dot

> notation, the CFQUERY returns an error that it cannot find the "other"

table,

> depending on which datasouce you have indicated in the CFQUERY tag.

I'm pretty confident that you cannot use JOIN across 2 different database
systems (in your case SQL server and IBM DB2).

--
<mack />

[Chugglethwaite]

Hi Rob,

You could consider bringing the DB2 server in to SQL server as a linked
server, then you can query it directly from SQL server in a single query using
dot notation (or OPENQUERY() ).

Check out sp_addlinkedserver in books online.

Cheers

Andy

[Gary1]

Thanks to all for the tips. I finally found an easy way to resolve the
problem. One thing I've found with ColdFusion is that you can usually find a
solution, albeit sometimes creative solutions, if you give it enough thought.
Here's how I did it.

I run the first CFQUERY against the remote IBM/DB2 DB/server, and bring the
result set back to the local SQL Server. The result set contains two items, a
list of self-service unit IDs, and the count of how often they were used for
the dates requested in the user form. I created a dummy table on the local SQL
server to hold the result set (the 2 fields).

I then run a second query that uses INSERT INTO, to write the result set to
the table. Now, I can write a 3rd CFQUERY that reads and joins the remote data
and the local data using a LEFT OUTER JOIN. Works like a charm.

To ensure the query always starts with an empty table for holding the remote
data, I began the INSERT INTO query with a TRUNCATE TABLE command. This
ensures every time the query is run, you start with an empty table. Since this
query will be run about 200 times per day from over 100 users, every run starts
with an empty "dummy" table.

And TRUNCATE TABLE doesn't cause a big increase in SQL Server's transaction
log size. In fact, I ran over 100 tests of the query and the transaction log
didn't increase in size at all from it's original 1024K size.

The entire 3-query/report process works very fast, and is efficient. The
queries, which must read through several million records on each run, take less
than 5 seconds to produce the final report.

I realize I could have used the CREATE TABLE command, but having the table
already created seemed to work just as efficiently, if not more so.

This will solve some other problems I've had where I need to do LEFT OUTER
JOINS on 2 tables, where data from one table is on a remote, non-SQL Server
DB/server.

Again, thanks for all the tips. If anyone has any comments about this method,
I would be happy to hear from you. Thanks again,

GaryThanks:D