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
raj
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..?
raj
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
Twan
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp
http://msdn.microsoft.com/library/en-us/adminsql/ad_1_server_24tv.asp
…for further information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |