During development, you sometimes need to query remote databases.
Connect to remote SQL Server 2005 as an example to create a remote SQL query
.
I. Using a linked server is suitable for the scenarios where OLE DB data sources are frequently referenced.
1. Add a linked server
Exec sp_addmediaserver
@ Server = '1970. 85.10.3 ', -- Link server name
@ Srvproduct = NULL -- Database Service Product
Specific reference http://msdn.microsoft.com/zh-cn/library/ms190479.aspx2.
Set the default connection password for the linked server
Exec sp_add1_srvlogin
@ Rmtsrvname = '192. 85.10.3 ', -- log on to the remote SQL link Server
@ Useself = 'false ',
@ Locallogin = NULL, -- local Login Name
@ Rmtuser = 'sa ', -- logon SQL user name
@ Rmtpassword = '000000' -- logon remote SQL Password
Specific reference http://msdn.microsoft.com/zh-cn/library/ms189811.aspx
3. Query
SELECT * FROM OPENQUERY ([221.85.10.3] ,'select * from AdventureWorks.dbo.Orders' )
4. view all servers
select * from sys.servers
Specific reference http://msdn.microsoft.com/zh-cn/library/ms178530.aspx
5. Delete the linked server
sp_droplinkedsrvlogin '218.85.10.3',null
sp_dropserver @server = '218.85.10.3
Specific reference http://msdn.microsoft.com/zh-cn/library/ms186218.aspx
Http://msdn.microsoft.com/zh-cn/library/ms174310.aspx
For more information, see http://www.cnblogs.com/wayne-ivan/archive/2008/01/07/1028815.html
About visualized operations: http://blog.zol.com.cn/730/article_729015.html