I am developing VB .Net application in Visual Studio 2008(64bit), SQL Server 2008(64bit) and Windows Server 2008(64bit) environment.

I need to import an Excel and Flat files data into SQL Server database using SQL Query. I tried a lot with the following SQL Queries directly with SQL Server Management Studio,

1. SELECT * INTO  dbo.ExcelImportTest FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\PTPLDCT\DCT Test Datasource\DCT_Excel_Test_Sample_1.xls',
'select * from [Sheet1$]')

While executing i got an error as below,

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

2. I have also tried with MSDASQL provider,

Select * from openrowset('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};
Dbq=DCT_Excel_Test_Sample_1.xls;DefaultDir=D:\PTPLDCT\DCT Test Datasource\', 'Select * from [Sheet1$]')

Then i encountered the following error,

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

3. Finally i have tried the Query with Linked Server,

EXEC sp_addlinkedserver @server = 'Excel_Linked_Server',
@srvproduct = 'MSDASQL',
@provider = 'MSDASQL',
@datasrc = 'D:\PTPLDCT\DCT Test Datasource\DCT_Excel_Test_Sample_1.xls'


Then, i encountered the following error,

OLE DB provider "MSDASQL" for linked server "Excel_Linked_Server" returned message "[Microsoft][ODBC Driver Manager] Data source name too long".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Excel_Linked_Server".


Please anyone help me to solve these issues.


Thanks in Advance,

Jaffar Rabeek