Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 2 of 2
  1. #1
    Member dinologic's Avatar
    Join Date
    Jan 2003
    Posts
    54
    Member #
    539
    I'm finally taking the plunge to SQL 2K. I've been working with Access for a few years and have accumulated several databases all over our network. One of those databases contains information that is used by the others through linked tables. When I move to SQL, how should I handle this? Can you query more than one database with one query? Is there a similar feature in SQL Server to allow one database to be "linked" to another? Or should I just create one huge database? (By "huge" I mean many tables, but record-wise, the largest table probably takes on about 30-40k records each year.)

    Any info here would be much appreciated.

    Thanks.

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    You can have "linked servers", which will treat multiple physical (or logical) database servers as one machine. When you get to building your query, though, you will have to reference cross-database queries like this:
    Code:
    SELECT
        p.name,
        a.address
    FROM
        human_resources.dbo.person AS p
        INNER JOIN mailing_room.dbo.address AS a
            ON p.ssn = a.ssn
    In this query, the name "human_resources" or "mailing_room" would represent your database (aka catalog) name. The "dbo" would represent the object owner (the user role in the database who owns the object. "dbo" is default. You can also leave it blank for dbo - i.e. "human_resources..person"). The "person" or "address" would represent the name of the table. "p" and "a" are aliases to the tables, and so on.

    This assumes that all of your databases (catalogs) are on one server or multiple linked servers.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:04 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com