SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Oracle Instant Client on SQL 2008 R2 64bit


Oracle Instant Client on SQL 2008 R2 64bit

Author
Message
Patti Johnson
Patti Johnson
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 950
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28243 Visits: 39955
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Patti Johnson
Patti Johnson
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 950
Thank you Lowell. It looks like you have installed the Oracle full client not the Oracle Instant client. Is that correct?
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28243 Visits: 39955
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Patti Johnson
Patti Johnson
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 950
Awesome. I will try that SQL and let you know.
Patti Johnson
Patti Johnson
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 950
Oh well. I was hoping.

I've attached my error
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search