none
linked server to access

    Question

  • I have a linked server on an sql 2008 R2 database to an access database.   I have a process that copies data to the access database.   If it runs from an sql agent job, it works.   If I take the same code and try to run within a query window in management studio, I get the following error.   Any idea why it will work in an sql agent job but not management studio query?

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server
    "sqltoaccesslinkedserver" returned message "Unspecified error".
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider
    "Microsoft.ACE.OLEDB.12.0" for linked server "sqltoaccesslinkedserver".

     

     

    Monday, October 17, 2011 2:36 AM

Answers

  • Hi aqlguy10,

    The problem might be that the service account is lack of permissions on the location of temp files which are generated during the operating. Please try to grant full access to everyone on the temp location. The directory is variable based on the types of operating system. For Windows Server 2008 R2, it is C:\Users\<SQL Service account name>\AppData\Local\Temp.

    Best Regards,
    Stephanie Lv

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Stephanie Lv Monday, October 24, 2011 11:45 AM
    • Marked as answer by Stephanie Lv Wednesday, October 26, 2011 8:55 AM
    Tuesday, October 18, 2011 3:32 AM

All replies

  • I am not sure if this will work, but go to the Providers folders insidel Linked server. select the Access provider and check the option "Allow in Process"

    I once had the same problem with DB2 provider and i recollect this erro rmessage and this is what i did to fix it.


    Manishankar Rajagopal | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Monday, October 17, 2011 2:41 AM
  • looks like it's already checked
    Monday, October 17, 2011 2:42 AM
  •  

    Check the UserName that is Running on SQL Job and make sure that your are Running on Management Studio with Same Credetials


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::
    Monday, October 17, 2011 2:53 AM
  • Create the database manually (on SQL Server 2005), then right-click the DB (on SQL Server 2005) and choose Task, Import Data, drill into your Access DB and import the tables. Once imported you can create a Database diagram (on SQL Server 2005) since all constraints will now be handled by SQL server. (make sure you key all of the tables)

    Then create an ODBC file DSN for this database connection (to SQL Server 2005), then fire up the Access database and right-click in the ‘Tables’ window, choose Link Tables, choose ODBC Databases() and then choose the DSN you created (to SQL Server 2005 database) and finally all of the tables you need to link.

    You’ll want to rename the old tables to “tableName_old” then rename the linked tables removing the “dbo_” from the table names.

    Linked server

    You can add a Access database to a SQL Server database. Following steps are -

    (1) Open EM.
    (2) Goto the Server to which you want to add it as linked server.
    (3) Then goto security > Linked Servers section from console tree.
    (4) Right click on the Client area. Then New Linked Server.
    (5) Give a name and Specify Microsoft Jet 4.0 as Provider string.
    (6) Prvide the location of the MDB file.
    (7) Click OK.


    Percentage Calculator Date Calculator Age Calculator
    Monday, October 17, 2011 10:13 AM
  • the user has admin on the server and sysadmin on the database
    Monday, October 17, 2011 1:18 PM
  • Hi aqlguy10,

    The problem might be that the service account is lack of permissions on the location of temp files which are generated during the operating. Please try to grant full access to everyone on the temp location. The directory is variable based on the types of operating system. For Windows Server 2008 R2, it is C:\Users\<SQL Service account name>\AppData\Local\Temp.

    Best Regards,
    Stephanie Lv

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Stephanie Lv Monday, October 24, 2011 11:45 AM
    • Marked as answer by Stephanie Lv Wednesday, October 26, 2011 8:55 AM
    Tuesday, October 18, 2011 3:32 AM