none
Access Linked Server error "The provider did not give any information about the error."

    Question

  • I'm running SQL SERVER 2008 R2 64bit. I have Microsoft.ACE.OLEDB.12.0 64 bit provider installed.

    On my production server I've setup a linked server to the access db using the provider listed above and everything works great.

    Copy the Access db to our dev environment and try to setup linked server useing the same provider. Test Connection comes back as success Select from linked server comes back with the following error:

    Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "CRM" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "CRM".

    Both servers are running same version of SQL Server. Both are Windows NT 6.1 64bit OS.

    I've triple checked and I did install the 64 bit version of the Access Database Engine.

    I've checked my registry and the key entries for allow inprocess and dynamic parameters are being set correctly.

    When I set AllowInProcess to 1 the test connection fails with the following error.

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    The test connection to the linked server failed.

    ------------------------------
    ADDITIONAL INFORMATION:

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

    ------------------------------

    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "CRM".
    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "CRM" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

     

    I followed the directions found as a suggestion here http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/12717aca-bac0-4c53-849b-e0a92383e7ae and created an system DSN for the access database.  When I follow those directions I get the following error when attempting to create the linked server.

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    The linked server has been created but failed a connection test. Do you want to keep the linked server?

    ------------------------------
    ADDITIONAL INFORMATION:

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

    ------------------------------

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "CRM2".
    OLE DB provider "MSDASQL" for linked server "CRM2" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
    OLE DB provider "MSDASQL" for linked server "CRM2" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
    OLE DB provider "MSDASQL" for linked server "CRM2" returned message "[Microsoft][ODBC Microsoft Access Driver] Your network access was interrupted. To continue, close the database, and then open it again.". (Microsoft SQL Server, Error: 7303)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

    ------------------------------
    BUTTONS:

    &Yes
    &No
    ------------------------------

    Any Help would be greatly appreciated.

    Thanks,

    Chris

     

     

     

    Wednesday, October 26, 2011 3:11 PM

Answers

  • I found the issue but I'm not sure why its happening.

    To query the linked server I have to right click SSMS and select Run as Admin. Then I can query the access db.

    I dont know why this is the case as I'm remoted into the machine. I'm an administrator on the machine. I created the Linked Server to the Access Database which resides on the C:\Drive of the server.

    But this will at least allow me to hand run my code. Since this is my development server I think that will suffice at least for now.

    • Marked as answer by creffner Thursday, October 27, 2011 7:29 PM
    Thursday, October 27, 2011 7:29 PM