Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 5th, 2003, 06:30 PM
mcp mcp is offline
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default How 2 query tables from 2 different SQL7 databases

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.
Reply With Quote
  #2 (permalink)  
Old June 5th, 2003, 06:47 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old June 5th, 2003, 07:42 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old June 5th, 2003, 08:32 PM
mcp mcp is offline
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Dave
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
using arrays for tables in databases Karla Visual Basic 2005 Basics 1 August 5th, 2008 02:11 PM
Report from two tables in two differents databases cphspain Crystal Reports 1 November 21st, 2006 05:13 PM
Link tables from two different SQL Databases Folorunso Agbeja SQL Server 2000 1 October 19th, 2006 08:23 AM
How can I merge two databases and tickets tables? drarem Access VBA 2 October 5th, 2004 01:07 PM
Comparing tables from two Databases lguzman Access VBA 1 May 11th, 2004 05:11 PM



All times are GMT -4. The time now is 08:55 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.