InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 22:47:29 GMT20http://www.sqlservercentral.com/Forums/Topic1198283-1044-1.aspxFor completeness, I had the following error message when trying to query the tables:Msg 7318, Level 16, State 1, Line 1The 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]Thu, 29 Nov 2012 07:55:07 GMTpeter Vrambyhttp://www.sqlservercentral.com/Forums/Topic1198283-1044-1.aspxHello 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 SqlPlus2) Create an ODBC System DSN3) Create a Linked server in Management Studio using the following code[code="sql"]EXEC sp_addlinkedserver @server = '{Linked Server Name}' ,@srvproduct = '{System DSN Name}' ,@provider = 'MSDASQL' ,@datasrc = '{System DSN Name}'[/code][b]WARNING:[/b]the [b]{System DSN Name}[/b] must be the exact same as the one you created in the ODBC4) Create the Remote Login user[code="sql"]EXEC sp_addlinkedsrvlogin @rmtsrvname = '{Linked Server Name}' ,@useself = 'False' ,@locallogin = NULL ,@rmtuser = '{Oracle User Name}' ,@rmtpassword = '{Oracle User Password}'[/code]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,PeterThu, 29 Nov 2012 06:41:50 GMTpeter Vrambyhttp://www.sqlservercentral.com/Forums/Topic1198283-1044-1.aspxOk, 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.Tue, 01 Nov 2011 11:36:16 GMTcoder_t2http://www.sqlservercentral.com/Forums/Topic1198283-1044-1.aspxCan'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.Tue, 01 Nov 2011 09:02:05 GMTcoder_t2http://www.sqlservercentral.com/Forums/Topic1198283-1044-1.aspx[quote][b]coder_t2 (10/31/2011)[/b][hr]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 1OLE 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.[/quote]Have you had the opportunity of checking this? [url]http://sqlservercorner.blogspot.com/2009_03_01_archive.html[/url]Mon, 31 Oct 2011 17:00:18 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1198283-1044-1.aspxHey 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 1OLE 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.Mon, 31 Oct 2011 13:30:22 GMTcoder_t2