SQL Server multiple ways to connect across servers

This article summarizes the way SQL Server connects across servers.

1, OpenDataSource

Open the database example directly with this statement in SQL:

OPENDATASOURCE(
'SQLOLEDB','Data Source=TQDBSV001
;User ID=fish;Password=2312').RackDB.dbo.CS

This approach is relatively simple, but there is the disadvantage is that the speed is very slow.

2, OPENROWSET

Includes all the connection information needed to access remote data from an OLE DB source. This approach is an alternative to connecting the server to the table, and is a specific way to connect and access remote data using OLE DB. The OPENROWSET function can be referenced as a table name in the FROM clause of the query. Depending on the capabilities of OLE DB Provider, the OPENROWSET function can also be referenced as a target table for an Insert, Update, or Delete statement. Although the query may return multiple result sets, OPENROWSET only returns the first result set. Grammar:

OPENROWSET ( 'provider_name'
　　, { 'datasource' ; 'user_id' ; 'password'
　　　　| 'provider_string' }
　　, { [ catalog.] [ schema.] object
　　　　| 'query' }
　　)

Parameters:

' provider_name ': A character string that represents the affinity name of the OLE DB provider specified in the login.

' provider_name ' has no preset value.

' DataSource ': a string constant that corresponds to a particular OLE DB data source.

' DataSource ' is the Dbprop_init_datasource property that is to be routed to the provider's IDBProperties interface to initialize the provider. Generally, this string includes the database file name, the database server name, or the provider's knowledge and use to find the name of the database.

' user_id ': is the user name string constant to be routed to the specified OLE DB Provider. USER_ID Specifies the security content of the line and is routed with the Dbprop_auth_userid property to initialize the provider.