Declare @ serverip varchar (50) -- Server IP Address
Declare @ serverothername varchar (50) -- alias of the accessed Server
Declare @ loginuser varchar (50) -- account
Declare @ loginpassword varchar (50) -- Password
Set @ serverip = '192. 8.28.9'
Set @ serverothername = 'neww'
Set @ loginuser = 'testuser'
Set @ loginpassword = '20140901'
-- Add a linked server
Exec sp_addmediaserver
@ Server = @ serverothername,
@ Datasrc = @ serverip,
@ Srvproduct = '',
@ Provider = 'sqlodb'
-- Add logon for the linked server
Exec sp_add1_srvlogin
@ Serverothername,
'False ',
Null,
@ Loginuser,
@ Loginpassword
-- Execute Query
Select * from [NEWW]. databasename. DBO. Table1
-- Delete a linked server
If exists (select srvname from Master. DBO. sysservers SRV where srvid! = 0 and srvname = n' neww ')
Exec master. DBO. sp_dropserver @ Server = n'neww', @ droplogins = 'droplogins'
Select * From openquery (NEWW, 'select top 5 * From table1 ')
Select top 5 * From OpenDataSource ('sqloledb', 'Data source = 118.8.28.9; user id = testuser; Password = 100'). hfoa2007.dbo. Table1
Select * From OpenRowSet ('msdasql ', 'driver = {SQL Server}; server = 118.8.28.9; uid = testuser; Pwd = 100', 'select top 5 * From table1 ')
/*
The openquery and OpenRowset methods directly transmit distributed queries, which is faster than directly using linked servers. The difference between openquery and OpenRowset is that openquery uses the link service instead of OpenRowSet.
When you use OpenRowSet () to execute the update command, you should use this function as the table to be modified. As follows:
Update OpenRowSet ('msdasql ', 'driver = {SQL Server}; server = 118.8.28.9; uid = testuser; Pwd = 100', 'select * From Table1 where id = 1 ') set fieldname1 = 'hello'
*/