InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 16:17:59 GMT20http://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspx[quote][b]JunkMail Victim (8/10/2011)[/b][hr]Hello aleksey2001.I do internal audit work. My ability to work with information through SQL Server is much greater than in Oracle. Collecting Oracle environmental data through SQL Server is a blessing. So far, I get the results every bit as fast through this method as I do when checking the SQL Server environment alone. Even if it was slow, I could wait.Everything has its purpose and benefit. This occurs irrespective of your personal wants and desires.I'm still very glad for the information.[/quote]Hi JunkMail Victim,Unfortunately not all users are as patient as you are. They are not willing to wait for several hours to transfer data. I'm not trying to say that it wouldn't work for anybody. It's just I really frustrated with slowness of that configuration at this moment because I need to deal with flat files transfer via SSIS job which need to be run from .Net application. It sucks big time. BTW I love audit options of ORACLE and kind of confused with SQL Server . Check out the fine grained auditing in Oracle. It's really neat.Wed, 10 Aug 2011 12:26:05 GMTaleksey2001http://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxHello aleksey2001.I do internal audit work. My ability to work with information through SQL Server is much greater than in Oracle. Collecting Oracle environmental data through SQL Server is a blessing. So far, I get the results every bit as fast through this method as I do when checking the SQL Server environment alone. Even if it was slow, I could wait.Everything has its purpose and benefit. This occurs irrespective of your personal wants and desires.I'm still very glad for the information.Wed, 10 Aug 2011 11:45:37 GMTJunkMail Victimhttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspx[quote][b]JunkMail Victim (6/6/2011)[/b][hr]Great article. It's so much easier to gather Oracle information from SQL Server than Oracle itself.[/quote]Oh yeah, it's ease and PAINFULLY SLOW.I used this configuration many times in different environments and was happy, but in our current environment we have a need to move ~ 1 million rows from SQL Server to Oracle and this job takes several hours to complete. To unload this data to plain text it takes 20 seconds. To load this data from plain text to Oracle it takes several minutes, so simple INSERT ... SELECT ... is a killer. Good luck with that configuration! And what's wrong with Oracle-to-Oracle? DBLINK works just fine.Tue, 09 Aug 2011 17:55:33 GMTaleksey2001http://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxIMO this should be the service name taken from tnsnames.oraTue, 07 Jun 2011 05:23:28 GMTDamian Widera-396333http://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspx[quote][b]daveriya (6/6/2011)[/b][hr]if you installed oracle client on sql server ,then what is the use of if.u need to create linked server to oracle client and then oracle client make connection to oracle server[/quote]If you do not install the Oracle client on your SQL server then the link connection will not work or not work properly. When you create a "linked" connection in SQL server; the server itself is connecting to Oracle (or other SQL servers) and not your desktop. To understand how linked server connection works I recommend Book On Line (BOL). In this article I am assuming that you know how linked servers work. It's not hard just takes some time to read through.The only thing that the Oracle client is going to provide you is the correct drivers in order for SQL server to connect to Oracle.I hope his answers your question.Thanks,RudyMon, 06 Jun 2011 09:49:14 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxif you installed oracle client on sql server ,then what is the use of if.u need to create linked server to oracle client and then oracle client make connection to oracle serverMon, 06 Jun 2011 09:41:25 GMTdaveriyahttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxGreat article. It's so much easier to gather Oracle information from SQL Server than Oracle itself.Mon, 06 Jun 2011 06:13:54 GMTJunkMail Victimhttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxIn my article ORACLESRV1 is the server name. Maybe you should first try to setup the link connection with the GUI. Once you see it working then you can modify the script to fit your needs.Please verify:@server = your oracle server nameand@datasrc = service name located in your tnsnamesOne last thing, did you install the Oracle client onto the SQL server that you connected to? If you are still having issues you may have to talk to your Oracle DBA as I'm not sure what else to say.Thanks,RudyMon, 06 Jun 2011 05:37:05 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxRudy, the ORACLESRV1 is just the name of the linked server, that you can then reference in SQL - that shouldn't have any effect?Yes I have the Oracle client installedMon, 06 Jun 2011 05:28:30 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxYou need to change the "ORACLESRV1" parameter to your server name. Verify all your setting and try again. If you have an Oracle DBA than ask them for some help. Do you installl the Oracle client on your SQL server? I had lots of issues until I installed it. The installation must be on your server not your workstation.RudyMon, 06 Jun 2011 05:23:36 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxThought it was, still doesn't help me.I have the error Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLESRV1".OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLESRV1" returned message "ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)I think there must be something else preventing me from making the connection.As an aside I can connect fine to this Oracle instance using TOAD/ Oracle SQL Developer, so I know that I am not 'blocked' as such.Mon, 06 Jun 2011 05:19:33 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspx[quote][b]kevriley (6/6/2011)[/b][hr]In the definition of the linked server, is 'ORACLESP' the resolved name from tnsnames, or is it the actual server name? Or either?[/quote]This is the serivce name which is located in the TNSNAMES.Hope this helps,RudyMon, 06 Jun 2011 05:02:44 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxThank you Rudy, nice articleMon, 06 Jun 2011 02:50:54 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxIn the definition of the linked server, is 'ORACLESP' the resolved name from tnsnames, or is it the actual server name? Or either?Mon, 06 Jun 2011 02:49:36 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic1120025-471-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Monitoring/73741/">Using SQL Server to collect information from your Oracle server</A>[/B]Sun, 05 Jun 2011 23:20:54 GMTRudy Panigas