October 31, 2011 at 1:30 pm
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.
October 31, 2011 at 5:00 pm
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.November 1, 2011 at 9:02 am
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.
November 1, 2011 at 11:36 am
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.
November 29, 2012 at 6:41 am
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
November 29, 2012 at 7:55 am
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:
[Code="sql"]
SELECT * FROM OPENQUERY({my Oracle linked server},'select * from {schema}.{TableName}')
[/code]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy