Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with Linked Query Setup for Oracle Expand / Collapse
Author
Message
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
Posted Monday, October 31, 2011 5:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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
Posted Tuesday, November 1, 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
Posted Tuesday, November 1, 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
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: Today @ 2:03 AM
Points: 76, Visits: 175
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
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: Today @ 2:03 AM
Points: 76, Visits: 175
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