SQL Server accesses the remote database and linked server

There are three basic methods for connecting SQL server to a remote data source: OpenDataSource : The OpenDataSource function is used to specify connection information for a remote data source by specifying the ole db provider and an initialization string. openDataSource can be used directly within a select, insert, update, or delete statement. OpenRowSet : The OpenRowSet function is used to specify connection information for a remote data source and the name of an object that will return a result set (such as a stored procedure) or a query that will return a result set. like OpenDataSource, OpenRowSet can be used directly within a select, insert, update, or delete statement. Linked servers : A linked server is an object within SQL server that defines the Connection Properties of another SQL Server. when defined, queries can connect to the remote server using a four-part name, such assqlsrv1.adventureworks. person. contactthe four-part name identifies the server (sqlsrv1), the database (adventureworks), the schema (person), and the object (contact table ). linked servers are already ed in More depth in the final section of this chapter. The OpenDataSource and OpenRowset methods are generally used for temporary queries (ad hoc queries). If you need to frequently query remote data, we recommend that you create linked servers. However, by default, ad hoc query is disabled and must be manually started: sp_configure 'show advanced options', 1; goreconfigure; gosp_configure 'ad hoc distributed queries ', 1; goreconfigure; then, you can use OpenDataSource to query remote databases. The basic syntax of OpenDataSource is as follows: OpenDataSource (provider_name, init_string ). Example Code Select * From OpenDataSource ('sqlncl', 'Data source = sql08; Integrated Security = sspi '). sales. DBO. the preceding customers code reads the contents of the customers table of the sales database from the sql08 server. OpenRowSet can also be used to query remote databases. OpenRowSet is similar to OpenDataSource. The basic difference is that OpenRowSet always returns the result set, while OpenDataSource can return the result set, it can also be used to execute stored procedures. The basic syntax of OpenRowSet is as follows: OpenRowSet (provider_name, provider_string) sample code: Select Rs. * From OpenRowSet ('sqlncl', 'server = sql08; trusted_connection = yes; ', 'select * from sales. DBO. MERs ') as RS; Use linked server If you plan to query external or remote data sources multiple times, we recommend that you create a linked server. Although it takes some time to create a linked server, once created, you can easily use the four-part name to access the remote data source. Create and configure a linked server Generally, two steps are required: The first step is to create a linked server. You can use the sp_addlinkserver system stored procedure to create a linked server. The basic syntax is as follows: sp_addmediaserver [@ Server =] 'server' [, [@ srvproduct =] 'product _ name'] [, [@ provider =] 'provider _ name'] [, [@ datasrc =] 'data _ source'] [, [@ location =] 'location'] [, [@ provstr =] 'provider _ string'] [, [@ catalog =] 'catalog '] The sample script is as follows: Exec Sp_addmediaserver@ Server = 'Sqlsrv3',@ Srvproduct = N' SQL Server' You can also create a linked server on the SQL Server Management Interface, as shown in:

Right-click the node above and choose "New linked server ...", In the pop-up window, you can create a linked server. Step 2: Create a Logon account for the linked server. After the linked server is created, the next step is to create a Logon account for the remote server. Map the Logon account of the local SQL Server to the Logon account of the linked server.

You can use either of the following methods to create a Logon account of the linked server: sp_add1_srvlogin and sp_add1_srvlogin: sp_add1_srvlogin [@ rmtsrvname =] 'rmtsrvname '[, [@ useself =] 'true' | 'false' | 'null'] [, [@ locallogin =] 'locallogin'] [, [@ rmtuser =] 'rmtuser'] [, [@ rmtpassword =] 'rmtpassword'] sp_add1_srvlogin: Exec Sp_add1_srvlogin @ Rmtsrvname = N 'sqlsrv3' , @ Useself = N'false' , @ Locallogin = N 'sa' , @ Rmtuser = N 'remoteuser' , @ Rmtpassword = 'Passowrd' You can also set a Logon account on the SQL Server Management Interface, as shown in:
From: http://blog.entlib.com/EntLib/archive/2009/03/06/sql-serverlinked-server.aspx