Using sp_addlinkedserver to implement remote database linking

--View Current link situation:

SELECT * from Sys.servers;

--use sp_helpserver to display the available servers

Exec sp_helpserver

--Delete a link that already exists

Exec sp_droplinkedsrvlogin Server alias, Null
Exec sp_dropserver Server Aliases

--use sp_addlinkedserver to add links

EXEC sp_addlinkedserver
@server = ' 192.168.2.66 ',--the server alias being accessed (customarily using the target server IP directly, or taking an individual name such as: JOY)
@srvproduct = ' ',
@provider = ' SQLOLEDB ',
@datasrc = ' 192.168.2.66 ' --the server to be accessed

--Use sp_addlinkedsrvlogin to add user login links

EXEC sp_addlinkedsrvlogin
' 192.168.2.66 ', --the server alias being accessed (if the alias joy is used in the sp_addlinkedserver above, this is joy too)
' False ',
Null
' sa ', --account number
' test123 ' --Password

Use an example (Access database music on the target server to view the contents of the table test):

If the alias is the target server IP when the link is established, that is 192.168.2.66

The

SELECT * FROM [192.168.2.66]. [Music].dbo.test

If the alias is joy when establishing the link,

The

SELECT * FROM [JOY]. [Music].dbo.test

Problems you may encounter:
exec sp_dropserver ' JOY ' failed to execute,

Error message: There are still remote logins or links to the server ' JOY ' .

Workaround:

exec sp_droplinkedsrvlogin ' JOY ', null

exec sp_dropserver ' JOY '

Reprinted from: http://blog.sina.com.cn/s/blog_4c197d4201017aoq.html

Using sp_addlinkedserver to implement remote database linking