Results 1 to 8 of 8

Thread: table access

  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: table access

    Our env: SQL*Server on win2k server.

    Question is: I have table1 in first db and I would like to access this table in second db. In oracle, we create db links to achieve the same. How do we achieve the same in SQL Sever?

    Any help is greatly appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
       SELECT CONVERT(varchar(15),EmployeeID) AS EmployeeID
    	, LastName+', '+FirstName AS EmployeeFullName
    	, HireDate
         FROM Employees
    UNION ALL
       SELECT emp_id AS EmployeeID
    	, lname+', '+fname AS EmployeeFullName
    	, hire_date AS HireDate
      FROM pubs.dbo.Employee
    ORDER BY 2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That is the best way if the two databases are on the same server. If they are on different servers, then you will need to create a linked server connection and use the OPENQUERY statement to access the remote data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If it's on another server, then that's a whole other ballgame.

    Your strategy then depends on what you are trying to accomplish.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Posts
    218
    many thanks!! for your response.

    Actually, it is in the same server. Scenario that Brett highlighted.

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    SQL server 2000 and earlier uses a 4 part naming convention for object identification.

    in oracle where you would use

    Code:
    select * from scott.emp
    in SQL Server you have the ability to name all of the elements in the object chain.

    SERVERNAME.DATABASENAME.OWNERNAME.OBJECTNAME

    in your case you want to access an object in another database, you would leave out the servername and use the rest of the convention.
    Code:
    mydb.user1.table1
    etc etc.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Ruprect
    .

    SERVERNAME.DATABASENAME.OWNERNAME.OBJECTNAME

    That's only with a linked server. And as I mentioned, there are a whole slew of issues to address.

    It is not as simple as just joing tables across 2 servers.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Quote Originally Posted by Brett Kaiser
    That's only with a linked server. And as I mentioned, there are a whole slew of issues to address.

    It is not as simple as just joing tables across 2 servers.
    didnt you read my thread?

    you should look again

    mydb.user1.table1 was the example that i gave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •