Cross-server SQL server operations

OpenRowSet

Contains all the connection information required to access the remote data in the ole db data source. This method is an alternative when accessing tables on the linked server. It is a one-time, special way to connect to and access remote data using ole db. The OpenRowSet function can be referenced in the from clause of the query as if the table name was referenced. Based on the capabilities of the ole db provider, you can also reference the OpenRowSet function as the target table of the insert, update, or delete statement. Although a query may return multiple result sets, OpenRowSet returns only the first result set.

Syntax

OpenRowSet('Provider_name'
,{'Datasource';'User_id';'Password'
|'Provider_string'}
,{[Catalog.] [Schema.]Object
|'Query'}
)

Parameters

'Provider_name'

String, which represents the friendly name of the ole db Provider specified in the registry.Provider_nameNo default value.

'Datasource'

String constant, which corresponds to a specific ole db data source.DatasourceYes will be passed to the providerIdbpropertiesInterface to initialize the dbprop_init_datasource attribute of the provider. Generally, this string contains the name of the database file, the name of the database server, or the name that the provider understands for searching the database.

'User_id'

String constant, which is the user name passed to the specified ole db Provider.User_idSpecify the security context for the connection and pass it as the dbprop_auth_userid attribute to initialize the provider.

'Password'

String constant, which is the user password that will be passed to the ole db Provider. When initializing the providerPasswordPassed in as the dbprop_auth_password attribute.

'Provider_string'

The specified connection string of the provider. It is passed as the dbprop_init_providerstring attribute to initialize the ole db Provider. UsuallyProvider_stringEncapsulate all connection information required to initialize the provider.

Catalog

Directory or database name, where the specified object resides.

Schema

The schema name or the name of the object owner of the specified object.

Object

Object Name, which uniquely identifies the object to be operated.

'Query'

Is a String constant that is sent to the provider and executed by the provider. Microsoft SQL Server does not process the query, but processes the query results returned by the provider (directly passing the query ). For some providers, they do not express their own table format data through the table name but through the command language, it is very useful to directly pass the query for these providers. As long as the query Provider supports OLE DBCommandObjects and their forced interfaces can be directly transmitted on the remote server. For more information, see SQL Server OLE DB programmer reference.

Note

If the ole db Provider supports multiple directories and architectures in the specified data source, the directory and architecture name are required. If the ole db provider does not support directories and architectures, You can omitCatalogAndSchema.

If the provider only supports the schema name, you must specify a two-part name in the formatSchema.Object. If the provider only supports directory names, you must specify a three-part name in the formatCatalog.Schema.Object.

OpenRowSet does not accept parameter variables.

Permission

The OpenRowSet permission is determined by the permission of the user name passed to the ole db Provider.

Example A. Use OpenRowSet with select statements and Microsoft ole db providers for SQL Server

The following example uses Microsoft ole db provider for SQL Server AccessPubsIn the databaseAuthorsTable namedSeattle1. SlaveDatasource,User_idAndPasswordAnd use the SELECT statement to define the returned row set.

USE pubsGOSELECT a.*FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',   'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS aGO

B. Use OpenRowSet with the object and the OLE DB provider for ODBC

The following example uses the ole db provider for ODBC and the SQL Server ODBC driver for accessPubsIn the databaseAuthorsTable namedSeattle1. Provided programs are used in the ODBC syntax specified by the ODBC providerProvider_stringTo define the returned row sets.Catalog.Schema.ObjectSyntax.

USE pubsGOSELECT a.*FROM OPENROWSET('MSDASQL',   'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',   pubs.dbo.authors) AS aORDER BY a.au_lname, a.au_fnameGO

C. Use the Microsoft ole db provider for jet

The following example uses the Microsoft ole db provider for jet to access Microsoft AccessNorthwindIn the databaseOrdersTable.

DescriptionThe following example assumes that access has been installed.

USE pubsGOSELECT a.*FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',    'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)    AS aGO

D. Use another table in OpenRowSet and inner join

The following example uses the local SQL ServerNorthwindDatabaseMERsTable and accessNorthwindDatabaseOrdersSelect all data in the table

DescriptionThe following example assumes that access has been installed.

USE pubsGOSELECT c.*, o.*FROM Northwind.dbo.Customers AS c INNER JOIN    OPENROWSET('Microsoft.Jet.OLEDB.4.0',    'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)    AS o