Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Oracle Instant Client on SQL 2008 R2 64bit Expand / Collapse
Author
Message
Posted Friday, July 26, 2013 6:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:39 PM
Points: 229, Visits: 803
Hi Everyone. I hope someone can help me quickly. I am really stuck.

I've installed the 64bit version of Oracle Instant Client basic, ODBC and SQLPlus packages on SQL 2008 R2 64bit development/test server . I created the ODBC system DSN. I created a TNSNAMES.ORA. I opened a CMD session and ran SQLPlus to confirm that I could communicate by logging into Oracle DB. I created the SQL Link using MSDASQL driver and tested the link. It worked perfectly. Programmers were able to use the link, access oracle data and we are good.

I do the exact same thing on production server. However, it breaks when test the SQL Link using MSDASQL driver. It gives the error: ORA-12541: TNS:no listener.

I don't get it if I can run SQLPlus from the CMD prompt and connect to the database you would think it would work when testing the SQL Link in SSMS.

Does anyone have any suggestions or ideas as to why it doesn't work now?

Please help, I'm desperate to figure this out. I am going to be the hold up with the project if I can't figure this out.

Thank you so much.
Patti
Post #1477959
Posted Friday, July 26, 2013 6:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
Patti I hope this helps:
this is a copy of the definition for scripting my linked server to Oracle; this is working for me, without using a DSN.

note that if your SQL server is 64 bit(most are nowadays), you had to install the optional 64 bit drives from Oracle,a nd not the default 32 bit drivers:

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

--#################################################################################################
--Linked server Syntax for Oracle 10G
--#################################################################################################
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 Oracle Database as a linked server
SET @server = N'MyOracle' --this is your Alias/NickName for it
SET @srvproduct = N'Oracle'
SET @provider = N'ORAOLEDB.Oracle' --optionally 'MSDAORA' to use the MS driver,but which fails on SELECTS featuring BLOBS/CLOBS
SET @datasrc = N'SFMN10G' --this is the SID/ServiceName
set @provstr = ''
EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr
-- exec sp_dropserver AccessDb
exec 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 names
EXEC sp_tables_ex 'MyOracle'
GO
EXEC dbo.sp_DropServer 'MyOracle', 'DropLogins'
/*my tnsnames.ora names definition for comparison/contrast:

--C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

sfmn10g2.disney =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBOra10g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sfmn10g)
)
)
*/



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477979
Posted Friday, July 26, 2013 10:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:39 PM
Points: 229, Visits: 803
Thank you Lowell. It looks like you have installed the Oracle full client not the Oracle Instant client. Is that correct?
Post #1478082
Posted Friday, July 26, 2013 10:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
Patti Johnson (7/26/2013)
Thank you Lowell. It looks like you have installed the Oracle full client not the Oracle Instant client. Is that correct?


Patti yes, that is correct; we just tend to install the fuller tools.

I googled SQL SERVER +"ORACLE INSTANT CLIENT" and only see one post that was reported working 100%; a lot report problems setting up, which i guess is typical.

make sure you run this for the driver just in case, and then create your linked server the way you were before, i think;
--Required settings for the provider to work correctly as a linked server
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'DynamicParameters', 1

a thread here on SSC also suggested using the full version of the drivers, but the OP posted that it worked for him after the commands above.

http://www.sqlservercentral.com/Forums/Topic1343393-1044-1.aspx#bm1349868

you could post a follow up in that thread, maybe the OP who installed the light client could script his linked server out as an example?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1478094
Posted Friday, July 26, 2013 10:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:39 PM
Points: 229, Visits: 803
Awesome. I will try that SQL and let you know.
Post #1478106
Posted Friday, July 26, 2013 10:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:39 PM
Points: 229, Visits: 803
Oh well. I was hoping.

I've attached my error
Post #1478108
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse