locked
ERROR: Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server"(null)" RRS feed

  • Question

  • Hi,

    I have been trying to export data from a Microsoft Excel 2007,  Tester.xlsx to a SQL Server 2005 table  as :

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

    I have an error:

    Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server"(null)"


    Tuesday, 25 December 2007 7:29 PM

Answers

All replies

  • Install the component. Then try Microsoft.ACE.OLEDB.12.0

    2007 Office System Driver: Data Connectivity Components: http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en


    Thanks,
    Zuomin


    Wednesday, 26 December 2007 1:07 AM
  • I have made the changes as follows:

    SELECT * INTO CUSTMAS FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Tester.xls;Extended Properties=Excel 8.0')...[Providers$]


    and, I'm still no nearer to solving this problems.


    The error  is as follows:


    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not contain the table " .
    The table either does not not exist or the current user does not have permissions on that table.


    Any suggestions?
    Wednesday, 26 December 2007 4:53 AM
  • I have now made changes to the code as follows:

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=C:\Tester.xlsx', 'SELECT * FROM [Sheet1$]');

    and, only to get the following error message:

    Error Source: .Net SqlClient Data Provider
    Error Message: The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

    Does this mean that I do not have enough login rights to the database? I'm using Windows Authentication, anyway.

    Any ideas, please?
    Wednesday, 26 December 2007 2:48 PM
  • I forgot to mention earlier that one of the columns has a timestamp datatype. Could this problem and the column with a timestamp datatype be linked ?
    Friday, 28 December 2007 10:36 AM
  •  

    Hi,

    Just wanted to make sure one thing, dbpath which you have supplied i.e. c:\tester.xlsx, is this path referred on your local machine or it's serverpath.

    Friday, 28 December 2007 11:37 AM
  • Hello,

    The path c:\tester.xlsx is on the local machine.
    Friday, 28 December 2007 6:18 PM
  • Just check if the first row of your excel file has columnname. if yes then user HDR=YES otherwise use HDR=NO.

    Make sure that column header should be in 1st row only.

     

    :) Happy Coding

    Ravi Thapliyal

    • Proposed as answer by Ravi Thapliyal Wednesday, 29 February 2012 3:53 AM
    Wednesday, 29 February 2012 3:53 AM
  • Alternate method is SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    The Wizard is a real gem of MS software engineering.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by Kalman Toth Friday, 16 March 2012 7:34 PM
    • Edited by Kalman Toth Sunday, 30 September 2012 7:23 AM
    Friday, 9 March 2012 2:02 PM
  • If that's just a single worksheet, then, yes, that would be a good method.

    If you can bear repeating the process of repeating the Import Wizard 30 or more times, then yes, that would be a good method to fall back on. 

    Me? I tried it and made mistakes in the repetitiveness of the task that I abandoned it.

    I would be opting to copy-paste the header rows containing the column names of the main sheet to another sheet, and then pivot paste them to another sheet, and name that sheet as "Columns".

    Then recreate the linked servers via script.

    I prefer iterations that require fewer steps. 

    Friday, 6 September 2019 4:24 AM