How 2 query tables from 2 different SQL7 databases

mcp · June 5th, 2003, 06:30 PM

I am using SQL 7. I have created 2 databases D1 & D2, each database has different tables. Using ASP Visual Basic Script I am tring to run a query that will join a table from D1 with one from D2.

Can someone tell me if this can be done and if so a direction of how to do it.

Thanks in advance.

David Cameron · June 5th, 2003, 06:47 PM

Use fully qualified names:

<database>.<owner>.<object>

eg
SELECT *
FROM D1.dbo.Table1 T1
INNER JOIN D2.dbo.Table2 T2 ON
T2.SomeID = T1.SomeID

regards
David Cameron

David Cameron · June 5th, 2003, 07:42 PM

Quote:

quote:
Thank you for your response. I understand the fully qualified names but have questions. If I have a connection Conn1 and Conn2 which one do I set the record set to. see below for example.
b1 & db2 are ODBC accessing respective ms sql 7 databases.

Conn1.Open "DSN=db1; UID=x; PWD=a"
Conn2.Open "DSN=db2; UID=x; PWD=a"

sql="Select tb1.*, tb2.* from Conn1.tb1, Conn2.tb2 Inner Join ..."
sql="Select tb1.*, tb2.* from Conn1.tb1, Conn2.tb2 Inner Join ..."

Set RS1 = Connx.execute

I probably am missing a big part of picture but this is how I generaly do it.

mcp, please ask questions in the forum rather than by email. This for two reasons.
1. Other people may be better able to answer the question, or may find an error with my suggestion.
2. Other people can learn from the question and/or answer.

You only need one connection. Even if you specifically connect to another database, assuming you have sufficient permissions, you should be able to still SELECT data from the other database. I just tested it then and had no problems. Just make sure the your login (for the database(s)) have the right permissions and that you get your SQL statement right.

regards
David Cameron

mcp · June 5th, 2003, 08:32 PM

Thanks that works great, never realized that it was just the default database in the ODBC and you could still access all databases.

Dave