Using the OPENROWSET function to operate the remote database

OPENROWSET

Contains all the connection information required to access remote data from an OLE DB data source. This approach is an alternative to accessing tables in a linked server, and is a one-time, special way to connect and access remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of the query as if it were a reference to a table name. Depending on the capabilities of the OLE DB provider, you can also refer to the OPENROWSET function as the target table for an INSERT, UPDATE, or DELETE statement. Although a query may return multiple result sets, OPENROWSET only returns the first.

Grammar

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

Parameters

' provider_name '

A string that represents the friendly name of the OLE DB provider specified in the registry. provider_name does not have a default value.

' DataSource '

A string constant that corresponds to a specific OLE DB data source. DataSource is the Dbprop_init_datasource property that will be passed to the provider IDBProperties interface to initialize the provider. Typically, this string contains the name of the database file, the name of the database server, or the name that the provider can understand to find the database.

' user_id '

A string constant that is the user name passed to the specified OLE DB provider. user_id Specifies the security context for the connection and passes it in as the Dbprop_auth_userid property to initialize the provider.

' Password '

A string constant that is the user password that will be passed to the OLE DB provider. When the provider is initialized, password is passed in as the Dbprop_auth_password property.

' provider_string '

Provides a provider-specific connection string that is passed in as the Dbprop_init_providerstring property to initialize the OLE DB provider. Typically provider_string encapsulates all the connection information that is required to initialize the provider.

Catalog

The name of the directory or database in which the specified object resides.

Schema

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

Object

The object name, which uniquely identifies the object that will be manipulated.

' Query '

is a string constant that is sent to the provider and executed by the provider. Microsoft? SQL Server? The query is not processed, but the query results returned by the provider are processed (pass-through queries directly). For some providers, it is very useful to use direct pass-through queries for these providers, rather than expressing their own tabular data in the command language through the table name. As long as the query provider supports OLE DB Command objects and their mandatory interfaces, direct pass-through queries are supported on the remote server. For more information, see SQL Server OLE DB Programmer's Reference.

Comments

If the OLE DB provider supports multiple directories and schemas in the specified data source, then the directory and schema name are required. If the OLE DB provider does not support catalogs and schemas, you can omit the catalog and schema values.

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

OPENROWSET does not accept parameter variables.

Permissions

The OPENROWSET permission is determined by the permission passed to the OLE DB provider's user name.

Example A. Use OPENROWSET with the SELECT statement and the Microsoft OLE DB provider for SQL Server

The following example uses the Microsoft OLE DB provider for SQL Server to access the authors table in the pubs database on a remote server named seattle1 . Initializes the provider from DataSource,user_id , and password , and uses the SELECT statement to define the rowset returned.

Use Pubsgoselect a.*from OPENROWSET (' SQLOLEDB ', ' seattle1 '; ' Sa '; ' Mypass ',   ' SELECT * from Pubs.dbo.authors ORDER by au_lname, au_fname ') as AGO

B. Using OPENROWSET with objects and OLE DB providers for ODBC

The following example uses the OLE DB provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database, which is in a database named seattle1 . The remote server. The provider is initialized with the provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog is used when defining the rowset that is returned. Schema. The object syntax.

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. Using the Microsoft OLE DB provider for Jet

The following example accesses the Orders table in the Microsoft Access Northwind database by using the Microsoft OLE DB provider for Jet.

Note The following example assumes that Access is already installed.

Use Pubsgoselect a.*from OPENROWSET (' microsoft.jet.oledb.4.0 ',    ' c:\MSOffice\Access\Samples\northwind.mdb '; ' Admin '; ' MyPwd ', Orders) as    AGO

D. Using another table in the OPENROWSET and INNER joins

The following example is from the customers table of the local SQL Server Northwind database, and the orders for the Access Northwind database stored on the same computer Select all data in the/c3> table

Note The following example assumes that Access is already 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 on   c.customerid = O.customerid GO

--Insert local data into server insert OPENROWSET (' SQLOLEDB ', ' 211.102.91.187 '; ' Www_dgcgh_com '; ' jshchg20050916 ', [www_dgcgh_com].dbo.product] SELECT * from product where id= ' 232 '

--inserting local data into the server

Insert OPENROWSET (' SQLOLEDB ', ' Remote server IP '; ' User name '; ' User password ', database name. dbo. Table name) select * FROM table name where id= ' 232 '

Using the OPENROWSET function to operate the remote database