Query two databases in asp and

Posted on 2009-04-16
Last Modified: 2012-06-27

I have two databases, DB1 and DB2
I need to make a query out of both see below statement sample of what i want to do:

SELECT FirstName "of DB2", LastName"of DB2",  FirstName "of DB1", LastName"of DB1"
FROM Mailing List "of DB2",  Mailing List "of DB1"
WHERE FirstName "of DB2" = FirstName "of DB1" ;

What do i need to do and how must my query look like in order to let the query work.

How do i need to create my connections for asp code and code as i need to make the query work in both languages?

Question by:eaweb
    LVL 25

    Accepted Solution

    Like this -
    SELECT DB2.FirstName , DB2.LastName,  DB1.FirstName , DB1.LastName
    FROM DB2..[Mailing List] DB2
    INNER JOIN DB1..[Mailing List] DB1 ON DB2.FirstName  = DB1.FirstName;

    Open in new window


    Author Comment

    is DB2..[Mailing List] syntax ok with ".." or has it to be "."

    and how must i setup my connection strings in asp and to let the query work?
    LVL 30

    Expert Comment

    by:Wayne Barron
    are you saying the you have
    2 Tables (In 1 database)
    2 Database's

    If it is 1 database with 2 tables. then

    "select db2.firstname, db2.lastname, db1.firstname, db1.lastname from mailing list where db2.firstname = db2.firstname"

    ALSO...  your Database Name     mailing list...
    Needs to be:                mailinglist
    1 word,,, not 2 words.

    Other then that, if it is 2 database's, then you will need to have a crash couse in database work.
    As you will only need 1 database, not 2.

    Good Luck
    LVL 15

    Expert Comment

    If it is two databases in sql server then you need to create a link server and make the query.
    LVL 25

    Expert Comment

    "is DB2..[Mailing List] syntax ok with ".." or has it to be ".""

    The syntax is perfectly fine for a database on a SQL Server backend. The square brackets are used when table/object names have embedded spaces. The ".." is just to ignore the owner of the table, you could also specify DB2.dbo.[Mailing List]

    Just so we are all clear here, what is the type of the database being used? Access / SQL Server??

    Carrzkiss -> Your sample SQL statement will not work on any backend and spaces in tablenames are perfectly permissible..

    spprivate -> A linked server may be required only if dealing with two databases in two different servers.
    LVL 30

    Expert Comment

    by:Wayne Barron
    Did you not read what I wrote?
    I stated: >>>  If it is 1 database with 2 tables. then
    And my SQL Statement. (I did leave out the extra tables in the statement)
    But, I was doing a example of IF it was a single DB.

    I went into a post a few months back, the Author posted the question almost identical as this one.
    And wanted to know how to write it up.
    I sent him over information on a Double DB Statement.
    He got aggrivated because I Could not read him mind....
    He had a single Database with 2 tables, not 2 Database's as he had originally wrote up.

    Every body makes mistakes, and I was not sure if "eaweb" had done the same thing
    So that is why I stated: 1 or 2 Database's in my post.

    And sorry for the missed up SQL Statement that I wrote.
    We all make mistakes right? Right.
    (Some of us just do not want to admit it)

    And spaces in table names are fine, BUT single names are better.
    And YES I do KNOW that you can use spaced names.

    Have a good one.
    LVL 25

    Expert Comment

    Carrzkiss ->

    "Needs to be:                mailinglist" "And spaces in table names are fine, BUT single names are better."

    Its just that "Need to be" hints at something being a requirement and not a recommendation, was just clarifying that spaces were permissible.. I agree using a single name without spaces is preferable, but is not mandatoryas your original post seems to suggest.

    "Did you not read what I wrote? I stated: >>>  If it is 1 database with 2 tables."

    I did, and the statement refers to a table 'mailing' which is aliased as 'list', refers to missing aliases db2 and db1 in the select list and checks for a condition which will always be true (db2.firstname = db2.firstname) if the aliasing were to be corrected.

    "He had a single Database with 2 tables, not 2 Database's as he had originally wrote up."

    The question and the sample SQL statement in the question makes it pretty clear to me that there are two different dbs with similarly named tables "mailing list".

    "We all make mistakes right? Right. (Some of us just do not want to admit it)"
    I do make mistakes and I'm humble enough to accept my mistakes when I notice it or it is pointed out to me.
    My post was not intended to be a personal attack on you, just pointed out that selected elements of the post were incorrect.


    Author Comment

    hi guys,

    i understand your issues, here some info:
    i am using mssql, my two databases are on one server and i have a query page in asp and

    how many connections do i need to to setup in asp and in to make it work and how must i do that (setup the connections) to let the query work in both pages?
    LVL 25

    Expert Comment

    "how many connections do i need"

    Just one connection will suffice.. The query in my first post (24160957) can be executed against this single connection

    "how must i do that (setup the connections)"

    What library are you using for ASP (ado?) and ASP.NET (ADO.NET?)? Depending on the library used to establish the connection, you can choose an appropriate connection string from the link -

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now