This chapter focused on how to read data from remote data sources and how to write data to remote data sources. Remote sources can be either another SQL Server instance or a different type of data source. You can do all of this using T-SQL. SQL Server Management Studio provides a user interface for managing remote sources.
To | Do this |
|---|
Execute infrequent queries against a remote resource by sending a pass-through query | Use the OPENROWSET function. |
Execute infrequent queries against a remote resource by referencing an object using its four-part name | Use the OPENDATASOURCE function. |
Create a new linked server using SQL Server Management Studio | In SQL Server Management Studio's Object Explorer window, expand a SQL Server instance, open the Server Objects folder, then open the Linked Servers folder. |
Create a new linked server programmatically | Use the sp_addlinkedserver system stored procedure. |
Remove a linked server programmatically | Use the sp_dropserver system stored procedure. |
Execute frequent queries against a remote resource by sending a pass-through query | Define a linked server and use the OPENQUERY function. |
Execute frequent queries against a remote resource by referencing an object using its four-part name | Define a linked server and use the linked server identifier as the <ServerName> part of the fully qualified name. |
Execute frequent DDL statements and stored procedures against a remote resource | Define a linked server and use the EXECUTE...AT construction. |