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