Post reply

Oracle Instant Client on SQL 2008 R2 64bit

  • July 26, 2013 at 6:20 am

    #296861

    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

    #1636131

    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!

  • July 26, 2013 at 10:00 am

    #1636221

    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

    #1636232

    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!

  • July 26, 2013 at 10:31 am

    #1636244

    Awesome. I will try that SQL and let you know.

  • July 26, 2013 at 10:36 am

    #1636246

    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