How to use OPENDATASOURCE

Provides ad hoc connection information as part of a four-part object name without using a linked server name
Syntax:
    OPENDATASOURCE ( provider_name, init_string )

Arguments

provider_name
Is the 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 the connection string passed to the IDataInitialize interface of the destination provider. The provider string syntax is based on keyword-value pairs separated by semicolons, such as: 'keyword1=value;keyword2=value'.

For specific keyword-value pairs supported on the provider, see the Microsoft Data Access SDK. This documentation defines the basic syntax. The following table lists the most frequently used keywords in the init_string argument.

Keyword	OLE DB property	Valid values and description
Data Source	DBPROP_INIT_DATASOURCE	Name of the data source to connect to. Different providers interpret this in different ways. For SQL Server Native Client OLE DB provider, this indicates the name of the server. For Jet OLE DB provider, this indicates the full path of the .mdb file or .xls file.
Location	DBPROP_INIT_LOCATION	Location of the database to connect to.
Extended Properties	DBPROP_INIT_PROVIDERSTRING	The provider-specific connect-string.
Connect timeout	DBPROP_INIT_TIMEOUT	Time-out value after which the connection try fails.
User ID	DBPROP_AUTH_USERID	User ID to be used for the connection.
Password	DBPROP_AUTH_PASSWORD	Password to be used for the connection.
Catalog	DBPROP_INIT_CATALOG	The name of the initial or default catalog when connecting to the data source.
Integrated Security	DBPROP_AUTH_INTEGRATED	SSPI, to specify Windows Authentication

Examples

The following example creates an ad hoc connection to the Payroll instance of SQL Server on server London, and queries the AdventureWorks2008R2.HumanResources.Employee table. (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider.)
------------------
SELECT *
FROM OPENDATASOURCE('SQLNCLI',
'Data Source=London\Payroll;Integrated Security=SSPI')
.AdventureWorks2008R2.HumanResources.Employee

------------------
The following example creates an ad hoc connection to an Excel spreadsheet in the 1997 - 2003 format.
------------------
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;
------------------
Note: For more detail click here