Results 1 to 8 of 8

Thread: Creating Views

  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Exclamation Unanswered: Creating Views

    Hai Guys,

    Can i create a view by joining 2 tables that r on two different database.

    Good Day.
    Thanks.
    Shabu

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    yes, it's possible.

  3. #3
    Join Date
    Oct 2004
    Posts
    3
    How do i do this.
    I have one server with 2 databases.
    my DB server is Sqlserver2000.

    Please Help.

    Thanks .

  4. #4
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    One way of doing it.

    This should probably do it, if you have linked the servers ...

    create view NameOfTheView as
    select someField, someOtherField
    from
    ServerName1.DBName.dbo.TableName Server1,
    ServerName2.DBName.dbo.TableName Server2,
    where
    Server1.Field1 = Server2.Field1 and
    <insert some other suitable conditions here ...>

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Something like:
    Code:
    SELECT s.sysadmin, d.owner
       FROM msdb.dbo.sysdtspackages AS d
       JOIN master.dbo.syslogins AS s
          ON (s.name = d.owner)
    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    you need to run sp_addlinkedserver for the remote server
    then run sp_addlinkedsrvlogin to create login maps
    then invoke the four part name in the query

    BOOKS ONLINE{sp_addlinkedserver}
    BOOKS ONLINE{sp_addlinkedsrvlogin}
    BOOKS ONLINE{Distributed Queries}

    this can be done easily in the enterprise manager
    SECURITY>LINKED SERVERS

    this is kind of the way a partitioned view is created check out federated servers in books online

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Ruprect
    you need to run sp_addlinkedserver for the remote server
    then run sp_addlinkedsrvlogin to create login maps
    Did I miss a meeting somewhere? When did a second server join this discussion?

    -PatP

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Quote Originally Posted by Pat Phelan
    Did I miss a meeting somewhere? When did a second server join this discussion?

    -PatP

    whoops
    my bad

    ignore my pre-post-question post that i had just posted

Posting Permissions

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