Post reply

Unable to Linked Server

  • December 2, 2011 at 2:05 am

    #263925

    Hi All

    I am using SQL Server Database 2008 and I want to create a linked server and link it to Microsoft Access database and the access database file name is Nwind.mdb.

    I issue the command in the SQL server Management Studio like this

    execute sp_addlinkedserver 'North','OLE DB Provider for Jet',

    'Microsoft.Jet.OLEDB.4.0','d:\sql_server\Nwind.mdb'

    and receive the message

    Command(s) completed successfully.

    now i want to query the customers table in access database and issue the following command

    select * from North...customers

    when I click execute button then I receive the following message.

    Msg 7308, Level 16, State 1, Line 1

    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

    Please help me out

  • December 6, 2011 at 7:36 am

    #1417366

    Is this a 64 bit server?

    There are some issues in connecting on 64 bit SQL

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/4887d91f-6ac7-40c0-9fc8-5cdd0634e603, its about Vista x64 and SQL 2008 x64.

  • December 6, 2011 at 1:18 pm

    #1417642

    Thanks for the email, yes its a 64 bit.

  • December 7, 2011 at 1:22 am

    #1417827

    from the article there are three options

    install SQL 32bit instead of 64bit

    install SQL Express 32bit side by side your 64bit instance and use a three hop method 64bit to 32bit to Access/Excel

    or push the data from Excel/Access into SQL using SSIS or the ImportExport data wizard

    i've not yet seen a working solution for 64bit and linking to Excel/Office, would be nice to see if anyone else has managed to get a work around other than the three above

  • December 7, 2011 at 1:35 am

    #1417833

    Thanks for your valuable information, I am planning to change my SQL Server version and i think after that my problem will solve, however, i am very thankful to you to provide me such a valuable information.

  • December 7, 2011 at 1:44 am

    #1417836

    i wouldnt go down that route personally

    i would go for one of the second two, install express or push the data instead of pulling

    express will only be there as a middle man to read the data and wont cause any problems

  • December 7, 2011 at 3:21 am

    #1417903

    Thanks for your suggestion, actually I am new in SQL Server and I dont know the process of "push the data from Excel/Access into SQL using SSIS", If you can guide me how to push data from excel/access into SQL using SSID then I will definately use this route. Thanks

  • December 7, 2011 at 3:32 am

    #1417911

    easiest route is this

    1.create a new blank database in SQL

    2.right click the new db --> tasks --> import data

    3.follow the wizard step by step up to Save and Run package

    4.save the SSIS package to SQL server

    5.run the package immediately

    --If you need to do this more every day for example continue

    6.create a SQL Agent job with an SSIS task to execute the package you have saved above

    the other option is to use SSIS

    this is a bit more complex,

    1.open up BIDS (business intelligence development studio)

    2.create a SSIS solution

    3.create connection managers (1 for source, 1 for destination)

    4.use data flow tasks to copy individual tables from source to destination

    5.run the package

    6.upload the package to SQL

    7.create a SQL agent jon to run it, if it needs to be run more than once.

    SSIS is a powerful beast and is to complex to go into in detail in a forum.

    I would recommend purchasing a decent book on the topic, or doing Microsoft course number 6235 if you can.

    The quickest route is the import data wizard

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply