SQL Server Articles

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


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

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

OPENDATASOURCE function provides ad hoc connection information as part of a four-part object name without using a linked server name. OPENDATASOURCE 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.

Syntax of OPENDATASOURCE Function :

OPENDATASOURCE ( provider_name, init_string )

provider_name is a name registered as the PROGID of the OLE DB provider used to access the data source. provider_name is a char data type, with no default value.

init_string is a connection string passed to the IDataInitialize interface of the destination provider. The provider string syntax is based on keyword-value pairs separated by semicolons, that is, "keyword1=value; keyword2=value."

Examples of OPENDATASOURCE Function :

Example 1 : Use of OPENDATASOURCE function in select clause

SELECT   ContactName, CompanyName, ContactTitle
              'Data Source=ServerName;User ID=MyUID;Password=MyPass' ).Northwind.dbo.Customers

Above example accesses data from a Customers table on another instance of SQL Server.


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

Sarita Patel
Sarita Patel author of OPENDATASOURCE 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!