SQL Server Remote Login Server Management and query

SQL Server Remote Login Server Management and query

/* Data operations between databases on different servers */-- create a link to the server exec sp_add+server 'itsv', '', 'sqloledb ', 'remote server name or IP address 'exec sp_addjavassrvlogin 'itsv', 'false', null, 'username ', 'Password' -- Query example select * From itsv. database Name. DBO. table name -- import example select * into table from itsv. database Name. DBO. table name -- delete the linked servers exec sp_dropserver 'itsv' and 'droplogins' when they are no longer in use -- connect to the remote/LAN data (OpenRowSet/openquery/OpenDataSource) -- 1. OpenRowSet -- Query example select * From OpenRowSet ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. DBO. table Name) -- generate a local table select * into table from OpenRowSet ('sqlodb', 'SQL Server name', 'username', 'Password', database name. DBO. table Name) -- import a local table to a remote table insert OpenRowSet ('sqlodb', 'SQL Server name', 'username', 'Password', database name. DBO. table Name) Select * from local table -- update local table update B Set B. column A =. column A from OpenRowSet ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. DBO. table Name) as a inner join local table B on. column1 = B. column1 -- create a connection for openquery usage -- first create a connection to create the connection server exec sp_add+server 'itsv', '', 'sqloledb ', 'remote server name or IP address' -- Query select * From openquery (itsv, 'select * from database. DBO. table name ') -- import a local table to a remote table insert openquery (itsv, 'select * from database. DBO. table name ') Select * from local table -- update local table update B Set B. column B =. column B from openquery (itsv, 'select * from database. DBO. table name ') as a inner join local table B on. column A = B. column A -- 3. OpenDataSource/OpenRowSet select * From OpenDataSource ('sqlodb', 'Data source = IP/servername; user id = login name; Password = password '). test. DBO. roy_ta -- import a local table to a remote table