Accessing Database of Different Server. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Accessing Database of Different Server.

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. Eg. Select Col1,Col2
from ….Emp
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

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..?
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 Cheers
…for further information. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.