http://www.windowsitpro.com/authors/author/author/5777688/rsscomment/5777688en-USSun, 27 May 2012 07:40:28 GMTSun, 27 May 2012 07:40:28 GMThttp://www.windowsitpro.com/article/permissions/implementing-windows-authentication-for-oracle#commentsAnchorMon, 14 Apr 2008 14:07:17 GMT
Pretty good]]>ANTHONYMon, 14 Apr 2008 14:07:17 GMThttp://www.windowsitpro.com/article/permissions/implementing-windows-authentication-for-oracle#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorThu, 17 Nov 2005 09:26:12 GMT
Great article, this was exactly what I needed. But, having made the connection work, I found a new problem: I can query an Oracle table, but if I want to use SELECT * FROM table, it’s not always working. As soon as the number of fields exceeds eight (8), the query times out. If I use the same query, but with one field less in the SELECT statement, is gives me back the result in a second. It doesn’t matter which of the field I put in or leave out. Example: This works (8 fields): SELECT MEMBER_ID, GIVEN_NAME, FAMILY_NAME, NAME_PREFIX, EMAIL, STREET, TOWN, POSTCODE FROM dbora..USERNAME.MEMBER_DATA This doesn’t (9 fields): SELECT MEMBER_ID, GIVEN_NAME, FAMILY_NAME, NAME_PREFIX, EMAIL, STREET, TOWN, POSTCODE, COUNTRY FROM dbora..USERNAME.MEMBER_DATA Obviously, SELECT * FROM dbora..USERNAME.MEMBER_DATA doesn’t work either, since it has more fields than 8. I get the same results using OPENQUERY. In the table are currently only 11 records, so it assume it’s not the size of the table. I’m using SQL Server 2000 on Windows Server 2003, linking to an Oracle 9 database. Does anyone have any idea why this occurs? Thanks, Patrick]]>PatrickThu, 17 Nov 2005 09:26:12 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorFri, 02 Sep 2005 05:29:33 GMT
Hi, I have setup a linked server that intermittently crashes. It connects to an Oracle Database on an alternate server. My tnsnames.ora file is correct and the Linked server does periodically work.... I am not sure what triggers it but it crashes and gives the following error: Error 7399: OLE DB provider ’MSDAORA’ reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider ’MSDAORA’ IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.]. Can anyone help with this please ? Which is the correct configuration in terms of drivers to use and vendor. Are there any registry settings that need changing? Thanks, Simon. ]]>Anonymous User Fri, 02 Sep 2005 05:29:33 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorWed, 10 Aug 2005 19:22:36 GMT
Thanks article great. Also last user that posted set "Allow InProcess", worked prefect after recreating thru Enterprise with this option. Thanks]]>Anonymous User Wed, 10 Aug 2005 19:22:36 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/permissions/implementing-windows-authentication-for-oracle#commentsAnchorThu, 23 Jun 2005 09:56:46 GMT
Help get something working that had been bothering me for a long time]]>Anonymous User Thu, 23 Jun 2005 09:56:46 GMThttp://www.windowsitpro.com/article/permissions/implementing-windows-authentication-for-oracle#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorTue, 21 Jun 2005 02:22:11 GMT
Another comment about last post: i deleted the linked server and add it from begining but this time i did this: Method 2 Set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then click to select the Allow InProcess check box. ]]>Anonymous User Tue, 21 Jun 2005 02:22:11 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorTue, 21 Jun 2005 02:22:00 GMT
Answer for the last comment. I had the same prob.This one solved it: Message 3 Error 7302: Could not create an instance of OLE DB provider ’MSDAORA’ Make sure that the MSDAORA.dll file is registered correctly. (The MSDAORA.dll file is the Microsoft OLE DB provider for Oracle file.) Use RegSvr32.exe to register Microsoft OLE DB Provider for Oracle. If the registration fails, reinstall Microsoft Data Access Components (MDAC). For more information about MDAC, visit the following Microsoft Developer Network (MSDN) Web site: http://msdn.microsoft.com/data/Default.aspx Note If you use a third-party Oracle provider, and your Oracle provider cannot run outside a SQL Server process, enable it to run in-process by changing the provider options. To change the provider options, use one of the following methods. • Method 1 Locate the following registry key. Then, change the value of the AllowInProcess (DWORD) entry to 1. This registry key is located under the corresponding provider name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ProviderName • Method 2 Set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then click to select the Allow InProcess check box. ]]>Anonymous User Tue, 21 Jun 2005 02:22:00 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorSun, 12 Jun 2005 19:29:57 GMT
- SQL Server 2000 & Oracle 10g - Oracle10g Provider for OLE DB Version 10.1.0.4.0 Able to create linked server and view all tables. However, query give Server: Msg 7320, Level 16, State 2, Line 1 Could not execute query against OLE DB provider ’OraOLEDB.Oracle’. OLE DB error trace [OLE/DB Provider ’OraOLEDB.Oracle’ ICommandText::Execute returned 0x80040155]. Query is select count(DEPT_CODE) from ORADB..ORADBA.DEPARTMENTS Have changed the PATH. But still the same error. What other config did I miss out ? Pls help... Txs. ]]>Anonymous User Sun, 12 Jun 2005 19:29:57 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/permissions/implementing-windows-authentication-for-oracle#commentsAnchorTue, 05 Apr 2005 08:19:40 GMT
Just what I was looking for. Thanks.]]>Anonymous User Tue, 05 Apr 2005 08:19:40 GMThttp://www.windowsitpro.com/article/permissions/implementing-windows-authentication-for-oracle#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorWed, 30 Mar 2005 04:16:45 GMT
I have: Oracle 8i and SQL Server 2000. I want to create a link server with a Oracle Database. First: I try with the OLE DB Provider of Oracle. I think that all the necesary data is ok, because I can see all the tables of the Oracle Database in my SQL Server and I can create views of the tables, but when I do a select of a table with more of 100 register I obtain an error of the controler like: Servidor: mensaje 7399, nivel 16, estado 1, línea 1 El proveedor OLE DB ’OraOLEDB.Oracle’ informa de un error. El proveedor no proporcionó información acerca del error. Second: I try with the OLEDB Provider of Microsoft and I can to create the linked server, but whe I try to create a views of any table of the Oracle Data Base I obtain an error like: Servidor: mensaje 7399, nivel 16, estado 1, línea 1 El proveedor OLE DB ’MSDAORA’ informa de un error. El proveedor no proporcionó información acerca del error. I execute the register script for my windows 2000 correctly but I obtain this error too. What is the problem with the two OLE DB Provider? Thanks]]>Miguel AngelWed, 30 Mar 2005 04:16:45 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorFri, 04 Mar 2005 12:27:53 GMT
Very nice article. I established successfully connection to Oracle but I am unable to execute Stored Procedure. Any clue why?]]>Anonymous User Fri, 04 Mar 2005 12:27:53 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/sql-server/analyzing-linked-server-queries#commentsAnchorWed, 23 Feb 2005 01:44:24 GMT
I follow the instructions for calling a SP from a triger . What is strange is that i need to execute twice the update command from SQL Server to ber excuted ! Do you have any idea !!! Regards]]>Anonymous User Wed, 23 Feb 2005 01:44:24 GMThttp://www.windowsitpro.com/article/sql-server/analyzing-linked-server-queries#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorWed, 26 Jan 2005 08:56:46 GMT
Anonymous User above, Any query over a network with joins will take ages. If you join two tables, the number of "reads" is very roughly equal to the total number of rows multiplied together I think. Hence all these reads become network reads so it takes ages. Try creating a view in oracle and querying that instead - that should be quicker. Also if you want to get technical check the join order of the query plan. Anyway my comments - article is good, however I still can’t get it to work. Having changed the path variable does anyone know if I have to restart the SQL server? Cheers Will]]>Anonymous User Wed, 26 Jan 2005 08:56:46 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorThu, 06 Jan 2005 16:22:32 GMT
Hi : I am retriving data from a oracle database using sql server linked server. I found that when I run the query in sql plus (oracle) the query executes within seconds but when I am executing the same query using sql server query analyzer it is taking more than 10 min. What could be the problem. my query involves inner join on 4 tables with millions of data and distinct and where clause. I get 6 rows with the query I am using. ]]>Anonymous User Thu, 06 Jan 2005 16:22:32 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/permissions/implementing-windows-authentication-for-oracle#commentsAnchorTue, 28 Dec 2004 12:30:25 GMT
good ]]>Anonymous User Tue, 28 Dec 2004 12:30:25 GMThttp://www.windowsitpro.com/article/permissions/implementing-windows-authentication-for-oracle#commentsAnchorhttp://www.windowsitpro.com/article/systems-administrator/manipulate-oracle-with-sql-plus#commentsAnchorFri, 03 Dec 2004 21:15:18 GMT
I need the Username and Password to work in Orcle 9i Sql Plus.]]>Anonymous User Fri, 03 Dec 2004 21:15:18 GMThttp://www.windowsitpro.com/article/systems-administrator/manipulate-oracle-with-sql-plus#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorMon, 22 Nov 2004 06:52:42 GMT
In SQL Server I create view like "select * from..." source is from Oracle Table. When I update oracle table struture (like add a column) SQL Server (via Linked Server) doesn’t update schema...of course, I try refresh, but no effect... Somebody have an idea ? :) Thanks]]>Anonymous User Mon, 22 Nov 2004 06:52:42 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorWed, 17 Nov 2004 07:25:36 GMT
Outstanding article, I’ve linked multiple SQL Servers before but this time I was able to link to an Oracle 10g database -- good job!]]>Anonymous User Wed, 17 Nov 2004 07:25:36 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/sql-server/analyzing-linked-server-queries#commentsAnchorTue, 12 Oct 2004 03:52:37 GMT
Excellant one to understanding the connecting method of oracle database through SQL server using linked server]]>Anonymous User Tue, 12 Oct 2004 03:52:37 GMThttp://www.windowsitpro.com/article/sql-server/analyzing-linked-server-queries#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorMon, 13 Sep 2004 12:34:21 GMT
I agree with Samir.. The advice on the PATH variable was just what the doctor ordered.. Thanks again!!]]>GARYMon, 13 Sep 2004 12:34:21 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorFri, 02 Jul 2004 08:28:27 GMT
Fantastic!!! Truly useful. Saved a lot of time and frustration. the details on change in PATH file did the trick. Thanks a lot.]]>Samir Shah Fri, 02 Jul 2004 08:28:27 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorThu, 17 Jun 2004 06:32:27 GMT
Nice article, but as I am from Germany I have no clue where to find the PATH environment variable described above. Would be nice to get help, because I think this is where my problems comes from.]]>alexThu, 17 Jun 2004 06:32:27 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorSat, 12 Jun 2004 03:55:27 GMT
excellent]]>Aleksandar Jovanovic Sat, 12 Jun 2004 03:55:27 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorWed, 28 Nov 2001 10:09:41 GMT
What about wrapping a distributed transaction around multiple inserts into Oracle and an update into SQL? The MSDAORO OLEDB Provider seems to hurl upon initiating a distributed transaction around the whole lot. Any ideas?]]>Richard M. Bagdonas Wed, 28 Nov 2001 10:09:41 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorhttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchorMon, 12 Nov 2001 19:34:25 GMT
I liked you article. I have been working with SQL Server and Oracle linked in a production environment for 2 years now. You brought up the interesting use of a UDF for selecting from Oracle. I find that best performance for Inserts is achieved by the construct of : INSERT INTO OPENQUERY(ORACLEDB, ’Select ... FROM MYTABLE WHERE 1=2’) VALUES ( ... ) What do you think ?]]>Jason Nunn Mon, 12 Nov 2001 19:34:25 GMThttp://www.windowsitpro.com/article/configuration/sql-server-and-oracle-making-the-connection#commentsAnchor