SQL Server Articles

Submit Article
Home » Articles » SQL Server » Rowset Functions RSS Feeds


Posted By: Sarita Patel     Category: SQL Server     Views: 23505

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
                'DRIVER={SQL Server};SERVER=seattle1;UID=manager;PWD=MyPass',

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


Didn't find what you were looking for? Find more on OPENROWSET Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of OPENROWSET Function is from United States.
View All Articles

Related Articles and Code:

Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

No Comment Found, Be the First to post comment!