SQL Server Articles

OPENROWSET Function

This article explains about OPENROWSET function in sql server with examples.

OPENROWSET function is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name.

The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

OPENROWSET can be used to access remote data from OLE DB data sources only if the DisallowAdhocAccess registry option is explicitly set to 0. When this option is not set, the default behavior does not allow ad hoc access.

OPENROWSET does not accept variables for its arguments.

Syntax of OPENROWSET Function :

OPENROWSET ( 'provider_name'

, { 'datasource' ; 'user_id' ; 'password'

| 'provider_string' }

, { [ catalog. ] [ schema. ] object

| 'query' } )

provider_name is a character string that represents the friendly name of the OLE DB provider as specified in the registry. It has no default value.

datasource is a string constant that corresponds to a particular OLE DB data source.

userid is a string constant that is the username passed to the specified OLE DB provider.

password is a provider-specific connection string that is passed in as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider.

catalog is a name of the catalog or database in which the specified object resides.

schema is a name of the schema or object owner for the specified object.

object is a object name that uniquely identifies the object to manipulate.

query is a string constant sent to and executed by the provider.

Examples of OPENROWSET Function :

Example 1 : Use of OPENROWSET function in select clause

SELECT ContactName, CompanyName, ContactTitle

FROM OPENROWSET('MSDASQL',

'DRIVER={SQL Server};SERVER=seattle1;UID=manager;PWD=MyPass',

Northwind.dbo.Customers)

Above example returns Customers contactname, companyname and contacttitle by using the OLE DB Provider for ODBC and the SQL Server ODBC driver.