SQl cross-server query script example

1. Using OpenDataSource

Select top *from opendatasource (' SQLOLEDB ', ' Data source=ip address; User id= connection name; password= Connect user password '). Remote target database. dbo. Table name

2. Using a junction server

--Create Linkserver
exec sp_addlinkedserver ' alias ', ' ', ' SQLOLEDB ', ' IP address '

--Landing Linkserver
exec sp_addlinkedsrvlogin ' Alias ', ' false ', NULL, ' Connect user name ', ' Connect user password '

--Query
SELECT * from alias. Library name. dbo. Table name

--Remove linked server when no longer in use
exec sp_dropserver ' aliases ', ' droplogins '

SQl cross-server query script example