InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:03:19 GMT20http://www.sqlservercentral.com/Forums/Topic1396747-391-1.aspxThanks. I d did manage to solve it. Apparently I had a mismatch between the server name in my tnsnames.ora file and the server name in my script. All solved by the forum againThanksNiallMon, 13 May 2013 22:05:01 GMTniall.bairdhttp://www.sqlservercentral.com/Forums/Topic1396747-391-1.aspxORA-12154: TNS:could not resolve the connect identifier specifiedMeans you have not established basic client connectivity (as mentioned) before trying to create your linked server.You'll need to do some Oracle research on how to do this, or you can post a specific question.If you search on the ORA-12154 message above, you' should find many guides on how to use Oracle tools to troubleshoot connectivity.Mon, 13 May 2013 21:36:18 GMTnick.mcdermaidhttp://www.sqlservercentral.com/Forums/Topic1396747-391-1.aspxThere is more than one person watching this...I'm trying to work out what the "SID" is.Running the script gives me this:OLE DB provider "MSDAORA" for linked server "MyServerName" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "MyServerName".Sun, 12 May 2013 18:36:53 GMTniall.bairdhttp://www.sqlservercentral.com/Forums/Topic1396747-391-1.aspx[quote][b]SQLCrazyCertified (12/31/2012)[/b][hr]Hi Lowell,I don't exactly follow all the steps you mentioned above.Can you please provide step by step for example, I want to create a Linkedserver from SQL to Oracle.What are the steps involved from beginning to end and do I need to contact Oracle team to accomplish this?Thanks in advance.SueTons.[/quote]SueTons you've got to establish basic connectivity to the Oracle Server(s) on the SQL server itself just like any other desktop that connects. That means using Oracle tools, before you can can create a linked server that uses that connectivity. that's step one, and you certainly could get the Oracle team involved; they probably do the same step to every new machine that gets added to your company.It will absolutely require the Oracle Client tools installation from the Universal installer.once you've got that, editing the linked server example should be very straight forward.let us know where you are at so we can offer some better suggestions.[img]https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcSD2-QmGbSFtVyKj6Iloabaw1p56qmpSObZCfNHRIQFAvkBdXmo[/img]Tue, 01 Jan 2013 12:06:21 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1396747-391-1.aspxHi Lowell,I don't exactly follow all the steps you mentioned above.Can you please provide step by step for example, I want to create a Linkedserver from SQL to Oracle.What are the steps involved from beginning to end and do I need to contact Oracle team to accomplish this?Thanks in advance.SueTons.Mon, 31 Dec 2012 15:16:04 GMTSQLCrazyCertifiedhttp://www.sqlservercentral.com/Forums/Topic1396747-391-1.aspxit starts out pretty much like every other desktop install around the office that is connecting to Oracle.Use the oracle universal installer to install the client tools. See your It guys for it, it's a pretty big item to redownload.next you need the 64 bit drivers as well, if they are not part of the universal install(our installer seemed to only do the 32 bit, but it's been a while since i needed to install again) the 64 bit ones found here:[url]http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html[/url]in our office, we copy the TNSNAMES.ORA file and the SQLNET.ORA files from an existing installation (ie C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN) o the matching \NETWORK\ADMIN folder on the server that was created after installing the drivers.once that is set up, and using the tools like SQLDeveloper or SQLPlus to prove we can connect, via those programs to the Oracle Instances, it's just a matter of setting up the linked seerver.here's my working linked server example code:[code]--#################################################################################################--Linked server Syntax for Oracle 10G / 11--#################################################################################################DECLARE @server sysname, @srvproduct nvarchar(256), @provider nvarchar(256), @datasrc nvarchar(100), @location nvarchar(100), @provstr nvarchar(100), @catalog sysname, @sql varchar(1000)--add an access Database as a linked serverSET @server = N'MyOracle' --this is your ALiasSET @srvproduct = N'Oracle'SET @provider = N'ORAOLEDB.Oracle' --optionally 'MSDAORA' to use the MS driverSET @datasrc = N'SFMN10G' --this is the SIDset @provstr = ''EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr-- exec sp_dropserver AccessDbexec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself = N'FALSE',@locallogin = 'sa',@rmtuser = N'CHANGE_ME',--oracle username@rmtpassword = 'NotARealPassword' --oracle password--list all the tables and their namesEXEC sp_tables_ex 'MyOracle'--EXEC dbo.sp_DropServer 'MyOracle', 'DropLogins'GO[/code]Mon, 17 Dec 2012 15:34:01 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1396747-391-1.aspxYes, That is what I exactly want. What I meant was - Install Oracle client driver to be able to create a linked server using an a system DSN previously created using that driver.Thanks I'm looking forward to hear something from you regarding this.Thanks againMon, 17 Dec 2012 11:17:38 GMTAPA0876http://www.sqlservercentral.com/Forums/Topic1396747-391-1.aspx[quote][b]APA0876 (12/14/2012)[/b][hr]How to install an oracle linked server on SQL Server 2008 r2 failover cluster managerI'm reading some articles and applying them but with no result.First of all where can I get the ODBC driver free and the install, as I got the driver but does include the odbc_install.exeThanks for your help, this is driving me crazy.Thanks againAPA[/quote]You mean, you want to set up a linked server which connects to Oracle from SQL server right?SueTons.Sun, 16 Dec 2012 18:01:37 GMTSQLCrazyCertifiedhttp://www.sqlservercentral.com/Forums/Topic1396747-391-1.aspxHow to install an oracle linked server on SQL Server 2008 r2 failover cluster managerI'm reading some articles and applying them but with no result.First of all where can I get the ODBC driver free and the install, as I got the driver but does include the odbc_install.exeThanks for your help, this is driving me crazy.Thanks againAPAFri, 14 Dec 2012 10:27:21 GMTAPA0876