Multiple methods for SQL Server Cross-Server connection

This article summarizes the cross-Server connection method of SQL Server.

1. OPENDATASOURCE

Example of using this statement to open a database in SQL:

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

This method is relatively simple, but the disadvantage is that it is slow.

2. OPENROWSET

Including all connection information required to access remote data from the ole db data source. This method is an alternative to accessing a data table on the linked server, and is a specific method for connecting and accessing remote data using ole db. The OPENROWSET function can be used as a data table name in the FROM clause of the query. Based on the capabilities of the ole db Provider, the OPENROWSET function can also be used as an Insert, Update, or Delete declarative target data table for reference. Although the query may return multiple result sets, OPENROWSET only returns the first result set. Syntax:

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

Parameters:

'Provider _ name': character string that represents the affinity name of the ole db provider specified during logon.

'Provider _ name' has no preset value.

'Datasource ': The String constant corresponding to the special ole db data source.

'Datasource 'is transmitted to the provider IDBProperties interface to initialize the DBPROP_INIT_DATASOURCE attribute of the provider. Generally, this string includes the database file name, database server name, or the name that the provider understands and uses to find the database name.

'User _ id': the user name String constant to be transferred to the specified ole db Provider. User_id specifies the security content of the connection and transmits it as DBPROP_AUTH_USERID to initialize the provider.

'Password': the user password string constant to be transferred to the ole db Provider. When initializing the provider, the password is transmitted using the DBPROP_AUTH_PASSWORD attribute.

'Provider _ string': transmitted as the DBPROP_INIT_PROVIDERSTRING attribute to initialize the connection string of a specific provider of the ole db Provider. Provider_string generally contains all the connection information required when initializing the provider.

Catalog: Specifies the database directory or database name of an object.

Schema: Specifies the structure description or owner name of an object.

Object: the name of the object to be operated.

'Query': The String constant sent to the provider and executed by the provider. MicrosoftR SQL Server? This query is not processed, but the query results returned by the provider are processed (the query is passed ). It is useful to transmit queries to providers that only expose their table data via command language without passing through the table name. As long as the query Provider supports the ole db Command object and its forced interface, the remote server supports transferring queries. For more information, see SQL Server OLE DB Programmer's Reference.

1 2 3 Next Page

[Content navigation]
Page 1st: SQL Server Cross-Server connection methods	Page 2nd: SQL Server Cross-Server connection methods
Page 3rd: SQL Server Cross-Server connection methods