locked
Linked server to access error RRS feed

  • Question

  • I created a linked server form SQL 2008 to a accesss database.

    I can show the content of some of the tables in the access database.

    But when I try to open some other tables in the same access table it shows a error message:

    Enumerate columans failed for linkedServer 'CFCE', (Microsoft.SqlServer.Smo)

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

        Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CFCE". The provider supports the interface, but returns a failure code when it is used. (.Net SqlClient Data Provider)

     Can any body here help me?

    Thanks

     

    Friday, August 13, 2010 8:44 PM

Answers

  • Hi Jin,

    Lets check & configure below things.

     

    1) Enable "Allowinprocess" property for linked server "CFCE"

                             In object explored drill down

                                             Server Objects

                                                     Linked Servers

                                                                     Providers

                                                                               Microsoft.Jet.OLEDB.4.0

                             Right Click Select Properties

                             Check "Allow Inprocess"

                             Click OK

     

    2) Permissions

    "What permissions does the SQL Server local system account have? I'm ok on my laptop with the SQL Server started with "local system account" and my Windows login being MachineName\Cindy. When I tried against my SQL Express instance which was started using the "NT AUTHORITY\NetworkService" I had the same errors you report. "

    Basically, the account the SQL Server runs under needs to have permissions to the directory where the Access database files are stored.

    Let me know the results.

     

    Regards

    Gursethi

    • Proposed as answer by Tom Li - MSFT Tuesday, August 17, 2010 9:17 AM
    • Marked as answer by Tom Li - MSFT Thursday, August 19, 2010 12:40 PM
    Saturday, August 14, 2010 7:56 PM

All replies

  • Hi Jin,

    Lets check & configure below things.

     

    1) Enable "Allowinprocess" property for linked server "CFCE"

                             In object explored drill down

                                             Server Objects

                                                     Linked Servers

                                                                     Providers

                                                                               Microsoft.Jet.OLEDB.4.0

                             Right Click Select Properties

                             Check "Allow Inprocess"

                             Click OK

     

    2) Permissions

    "What permissions does the SQL Server local system account have? I'm ok on my laptop with the SQL Server started with "local system account" and my Windows login being MachineName\Cindy. When I tried against my SQL Express instance which was started using the "NT AUTHORITY\NetworkService" I had the same errors you report. "

    Basically, the account the SQL Server runs under needs to have permissions to the directory where the Access database files are stored.

    Let me know the results.

     

    Regards

    Gursethi

    • Proposed as answer by Tom Li - MSFT Tuesday, August 17, 2010 9:17 AM
    • Marked as answer by Tom Li - MSFT Thursday, August 19, 2010 12:40 PM
    Saturday, August 14, 2010 7:56 PM
  • Thanks for your  help... your response has fixed my issue..Thanks a Lot :) :)
    Tuesday, September 17, 2013 5:20 PM