July 26, 2013 at 6:20 am
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
July 26, 2013 at 6:55 am
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
July 26, 2013 at 10:00 am
Thank you Lowell. It looks like you have installed the Oracle full client not the Oracle Instant client. Is that correct?
July 26, 2013 at 10:15 am
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
July 26, 2013 at 10:31 am
Awesome. I will try that SQL and let you know.
July 26, 2013 at 10:36 am
Oh well. I was hoping.
I've attached my error
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply