locked
cannot update the database or object is read only RRS feed

  • Question

  • i am reading data from an excel file using OledbDataReader

    but it shows error "cannot update the database or object is read only".

    but in case of .csv file its working fine.

    can u tell me the actual problem ?

    Saturday, November 17, 2012 3:16 PM

Answers

  • Hi Ray,

    Sorry for the delay.

    According to the KB article, we should modify the regedit registry key instead of the connect string. If you use 64-bit Operating System, the target registry key is:

    HKEY_LOCAL_MACHINE\Software\WOW6432Node\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions

    For your convenience, I have included a screenshot to show how to modify the registry key:

    Regards,


    Mike Yin
    TechNet Community Support

    • Proposed as answer by Syed Qazafi Anjum Tuesday, November 27, 2012 2:38 AM
    • Marked as answer by Mike Yin Sunday, December 2, 2012 5:44 PM
    Tuesday, November 27, 2012 2:02 AM
  • Hi RRRay,

    The root cause may be that the Excel extension cannot be recognized by Jet, while the .csv name extension is allowed by default. To resolve the issue, please modify the following registry key to include the extension of the Excel file:
    HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions

    Note: Please back up the entire registry before making any modifications so that we can restore the registry if any problem occurs.

    For more information, please refer to the following article that is about the Access 2000:
    http://support.microsoft.com/kb/245407

    If you are importing the Excle files using Import/Export Wizard, I suggest that you also try to import the Excel fils to the SQL Server Database using T-SQL script. For the detailed information, please see:
    http://www.codeproject.com/Articles/21351/Import-Data-to-SQL-Server-from-Excel-or-Access-usi

    Hope this helps.

    Regards,


    Mike Yin

    TechNet Community Support

    • Marked as answer by Mike Yin Sunday, December 2, 2012 5:44 PM
    Monday, November 19, 2012 4:54 PM

All replies

  • Is office 2007?One option is convert your .xlsx file (2007) to .xls file (2003) and then run query.

    http://support.microsoft.com/kb/304146?wa=wsignin1.0


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Sunday, November 18, 2012 8:09 AM
  • The excel file is already in .xls format.
    Sunday, November 18, 2012 2:07 PM
  • Hi RRRay,

    The root cause may be that the Excel extension cannot be recognized by Jet, while the .csv name extension is allowed by default. To resolve the issue, please modify the following registry key to include the extension of the Excel file:
    HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions

    Note: Please back up the entire registry before making any modifications so that we can restore the registry if any problem occurs.

    For more information, please refer to the following article that is about the Access 2000:
    http://support.microsoft.com/kb/245407

    If you are importing the Excle files using Import/Export Wizard, I suggest that you also try to import the Excel fils to the SQL Server Database using T-SQL script. For the detailed information, please see:
    http://www.codeproject.com/Articles/21351/Import-Data-to-SQL-Server-from-Excel-or-Access-usi

    Hope this helps.

    Regards,


    Mike Yin

    TechNet Community Support

    • Marked as answer by Mike Yin Sunday, December 2, 2012 5:44 PM
    Monday, November 19, 2012 4:54 PM
  • My connection string is This one

    @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path2 + @";Extended Properties=""text;HDR=2;FMT=TabDelimiter"""

    what should i modify ?

    Tuesday, November 20, 2012 5:33 AM
  • Hi Ray,

    Sorry for the delay.

    According to the KB article, we should modify the regedit registry key instead of the connect string. If you use 64-bit Operating System, the target registry key is:

    HKEY_LOCAL_MACHINE\Software\WOW6432Node\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions

    For your convenience, I have included a screenshot to show how to modify the registry key:

    Regards,


    Mike Yin
    TechNet Community Support

    • Proposed as answer by Syed Qazafi Anjum Tuesday, November 27, 2012 2:38 AM
    • Marked as answer by Mike Yin Sunday, December 2, 2012 5:44 PM
    Tuesday, November 27, 2012 2:02 AM