SQL Server Performance

Accessing Database of Different Server.

Discussion in 'General DBA Questions' started by krajdba, Dec 8, 2004.

  1. krajdba New Member

    How to access database data of a different server?
    For Example : I have a table Emp in Server 2.From Server 1 I would like to access the emp in server 2.


    Select Col1,Col2
    from ....Emp

  2. Chappy New Member

    use a fully qualified name of SERVER.DATABASE.owner.OBJECT
    Using this method the server must be added as a linked server. Theres other ways to avoid this, look at OPENQUERY, OPENROWSET etc
  3. krajdba New Member

    When I am executing the following Query

    select * from [DATSVR].PreStaging..tblCompany

    I am getting this error message.

    Server: Msg 7202, Level 11, State 2, Line 1
    Could not find server 'DATSVR' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

    And when I Execute sp_addlinkedserver this I am getting rights or permissions problems.

    How to assign rights or permissions..?

  4. Twan New Member

    Hi Raj,

    only a sysadmin user can create new linked servers, so use the sa login or an account with the sysadmin role...? Adding linked servers is best done with EM where it is easy to specify who you want the connection to connect as...

    DATSVR would need to be the sql server's name using the default instance on that server, unless you use the client network utility to create an alias called DATSVR for a particular server/instance/port

  5. satya Moderator

Share This Page