OPENDATASOURCE

It provides ad hoc connection information as part of four – part object name without using a linked server name.

Syntax:
OPENDATASOURCE( provider_name, init_string )

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, that is, "keyword1=value; keyword2=value."

The following lists the most commonly used keywords in the init_string argument.

Keyword : Data Source
OLE DB property : DBPROP_INIT_DATASOURCE
Valid values and Description :
Name of the data source to connect to. Different providers interpret this in different ways. For SQL Server 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.

Keyword : Location
OLE DB property : DBPROP_INIT_LOCATION
Valid values and Description : Location of the database to connect to.

Keyword : Extended Properties
OLE DB property : DBPROP_INIT_PROVIDERSTRING
Valid values and Description : The provider-specific connect-string.

Keyword : Connect timeout
OLE DB property : DBPROP_INIT_TIMEOUT
Valid values and Description : Time-out value after which the connection attempt fails.

Keyword : User ID
OLE DB property : DBPROP_AUTH_USERID
Valid values and Description : User ID to be used for the connection.

Keyword : Password
OLE DB property : DBPROP_AUTH_PASSWORD
Valid values and Description : Password to be used for the connection.

Keyword : Catalog
OLE DB property : DBPROP_INIT_CATALOG
Valid values and Description : The name of the initial or default catalog when connecting to the data source.

Examples
This example accesses data from a table on another instance of SQL Server.

SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=ServerName;User ID=MyUID;Password=MyPass').Northwind.dbo.Categories

This is an example of a query against an Excel spreadsheet through the OLE DB provider for Jet.

SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

...S.VinothkumaR.

How to import data from Excel to SQL Server?

We can import data from Excel to SQL Server by using SQL Server distributed queries.

We can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples will help us to import data from excel customer’s worksheet into new SQL Server tables.

SELECT * INTO tblXLImport1 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO tblXLImport2 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls', [Customers$])

SELECT * INTO tblXLImport3 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls', 'SELECT * FROM [Customers$]')

//Here Customers$ means the name of worksheet.

Simple sample query:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\students.xls;Extended Properties=Excel 8.0')...[Students$]

...S.VinothkumaR.