Post reply

SQL to Oracle

  • September 13, 2006 at 2:26 pm

    #61337

    I am trying to connect to Oracle from SQL Server to access a view . Unfortunatley i dont have Oracle client installed on my machine. Is there any other way we can set up this connection? Any ideas or thoughts will be greatly appreciated.

    TIA

  • September 13, 2006 at 2:42 pm

    #660244

    How do you normally access this view?

    Regards,Yelena Varsha

  • September 14, 2006 at 4:31 am

    #660327

    You will need to install the oracle drivers on the sql server machine. A restart of sql server is than necessary.

  • September 15, 2006 at 7:20 am

    #660685

    I have oracle client installed locally on the server from where i am trying to connect to Oracle. Now when trying to set up the linked server I am not sure what gos into DataSource and Provider string section . I am tryingt  select "Microsoft  OLE DB provider for Oracle" under Provider name. Any suggestions or help regarding this will be greatly appreciated.

    TIA

  • September 15, 2006 at 7:56 am

    #660705

    a word-document how to set up an oracle linked server:

    http://www.ed.uiuc.edu/datamasters/Documents/GEN_SQLServerOracleLinkedServer.doc

    the microsoft way:

    http://support.microsoft.com/kb/280106/

  • September 18, 2006 at 8:47 am

    #661052

    Thanks for the link that really helped I am able to set up the linked server successfully and i can see the oracle view from sql server enterprise manager. However when i try from query analyser to see the contents that doesnt work. I tried select count(*) from [linked_servername].[databasename].[Viewname].

    I tried with openquery also but that didnt work either .

    ANy help or suggestions to get this displayed?

    TIA

     

  • September 18, 2006 at 9:50 am

    #661061

    I got it working using the following

    select * from openquery(linkservername,'select * from dbo.view').

     

    Thanks

  • September 18, 2006 at 9:50 am

    #661060

    Naming in Oracle is a little different.

    Try

    select count(*) from [linked_servername]..[OracleSchemaName].[Viewname].

  • September 18, 2006 at 12:25 pm

    #661109

    There are two ANSI SQL based functions OpenRowset and OpenQuery.  Try the links below for details.

    http://msdn2.microsoft.com/en-us/library/ms190312.aspx

    http://msdn2.microsoft.com/en-us/library/ms188427.aspx

    Kind regards,
    Gift Peddie

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply