Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » Working with Oracle » Help with Linked Query Setup for Oracle
Add to briefcase

Help with Linked Query Setup for Oracle Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
coder_t2
Posted Monday, October 31, 2011 1:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:49 PM
Points: 34, Visits: 109
Hey guys, I am trying to setup a linked query to an oracle server on my machine. I am using Sql Server 2008 and a 64-bit OS. I have installed the ODAC for windows and ODBC for RDB drivers for 64-bit windows. However I am getting the following error.

Msg 7308, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.'

Any ideas what I am missing? Thanks.
Post #1198283
PaulB-TheOneAndOnly
Posted Monday, October 31, 2011 5:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:08 PM
Points: 2,982, Visits: 4,396
coder_t2 (10/31/2011)
Hey guys, I am trying to setup a linked query to an oracle server on my machine. I am using Sql Server 2008 and a 64-bit OS. I have installed the ODAC for windows and ODBC for RDB drivers for 64-bit windows. However I am getting the following error.

Msg 7308, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.'

Any ideas what I am missing? Thanks.


Have you had the opportunity of checking this? http://sqlservercorner.blogspot.com/2009_03_01_archive.html

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1198380
coder_t2
Posted Tuesday, November 01, 2011 9:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:49 PM
Points: 34, Visits: 109
Can't access it at work. I'll see if I can get it unblocked, or I'll have to wait until I get home. Hopefully it helps.
Post #1198738
coder_t2
Posted Tuesday, November 01, 2011 11:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:49 PM
Points: 34, Visits: 109
Ok, got access to the website at work and it hasn't really helped. I am using Windows 7 64-bit, and I cannot find a 64-bit MDAC for it. From I can tell, MDAC is now WDAC and is part of the core components of Windows 7. So I tried using the OraOLEDB.Oracle, and I get the same error I posted in my first post.
Post #1198824
peter Vramby
Posted Thursday, November 29, 2012 6:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 30, 2012 1:21 AM
Points: 75, Visits: 160
Hello all,

I had the same problem here and after a lot of searching on the internet, I found a solution where you use the ODBC System DSN instead of the default possibilities that are present in the Management Studio. In short I did the following things:

1) Install the Oracle tools and be sure that I could connect to the Oracle Database using SqlPlus
2) Create an ODBC System DSN
3) Create a Linked server in Management Studio using the following code
EXEC sp_addlinkedserver 
     @server		= '{Linked Server Name}'
    ,@srvproduct	= '{System DSN Name}'
    ,@provider		= 'MSDASQL'
    ,@datasrc		= '{System DSN Name}'

WARNING:the {System DSN Name} must be the exact same as the one you created in the ODBC

4) Create the Remote Login user
EXEC sp_addlinkedsrvlogin 
     @rmtsrvname	= '{Linked Server Name}'
    ,@useself		= 'False'
    ,@locallogin	= NULL
    ,@rmtuser		= '{Oracle User Name}'
    ,@rmtpassword	= '{Oracle User Password}'

5) Test The connection (by querying the Oracle objects)
EXEC sp_tables_ex '{Linked Server Name}'

It took me 3 days to find this solution, so I post it here so that you can all enjoy the solution.

Greetings,
Peter
Post #1390505
peter Vramby
Posted Thursday, November 29, 2012 7:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 30, 2012 1:21 AM
Points: 75, Visits: 160
For completeness, I had the following error message when trying to query the tables:

Msg 7318, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "{my Oracle linked server}" returned an invalid column definition for table ""{schema}"."{TableName}"".

That was easily solved using the OPENQUERY command:
SELECT * FROM OPENQUERY({my Oracle linked server},'select * from {schema}.{TableName}')
Post #1390561
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.