We help IT Professionals succeed at work.

how can i solve “The OLE DB provider ”Microsoft.Jet.OLEDB.4.0“ has not been registered.”?

programmerist
programmerist asked
on

  i try to use get excel data from excel file. i am using office 2007 and sql 2005. i writed below codes:
CREATE TABLE [dbo].[Addresses_Temp] ( 
    [FirstName]   VARCHAR(20), 
    [LastName]    VARCHAR(20), 
    [Address]     VARCHAR(50), 
    [City]        VARCHAR(30), 
    [State]       VARCHAR(2), 
    [ZIP]         VARCHAR(10) 
) 
GO 
 
INSERT INTO [dbo].[Address_Temp] ( [FirstName], [LastName], [Address], [City], [State], [ZIP] ) 
SELECT [FirstName], [LastName], [Address], [City], [State], [ZIP] 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1', 
                'SELECT * FROM [Sayfa1$]') 

Open in new window


Error:Msg 7403, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
How can i solve it?
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Try to use Bulk Insert Option :


CREATE TABLE [dbo].[Addresses_Temp] (
    [FirstName]   VARCHAR(20),
    [LastName]    VARCHAR(20),
    [Address]     VARCHAR(50),
    [City]        VARCHAR(30),
    [State]       VARCHAR(2),
    [ZIP]         VARCHAR(10)
)
GO

BULK INSERT
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE  =
      { 'char' | 'native'| 'widechar' | 'widenative' } ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FIRSTROW  =first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
   [ [ , ] LASTROW = last_row ]
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] TABLOCK ]
   [ [ , ] ERRORFILE = 'file_name' ]
    )]

Example:  BULK INSERT [Addresses_Temp]
   FROM  'e:\Sayfa1$.xls'
   WITH
      (
         FIELDTERMINATOR =' ,',
         ROWTERMINATOR =' \n'

Author

Commented:
How can i write my codes like your codes? i can not rearrange it?

Author

Commented:
How can i send data from Excel to sql?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Have you tried installing latest Jet Driver from the links I posted above..
Top Expert 2012

Commented:
And we can only hope you are not using SQL Server 2005 64-bit...
Top Expert 2010

Commented:
If you are trying to send Data from Excel to SQL 2005, why dont you try it the other way round

Create an SSIS package in SQL and call it when needed,
its very simple to do so, just use the normal import/export wizard and save your package and your done.

then all you have to do is just call your dtsx package

Regards

Commented:
I'm thinking along the same lines as acperkins...
This sounds like you've got a 64-bit version of SQL Server.  If so, you'll have to install the new 64-bit version of the Microsoft Access Database Engine 2010 drivers.  This is the replacement for the OleDB/Jet drivers which are not available as 64-bit versions.
http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en