locked
linked server to an Oracle database RRS feed

  • Question

  • Hi,

    I am having some problems setting up a linked server to an Oracle database.

    I am using SQL Server 2000 and an ODBC connection to Oracle.  The ODBC connection works fine in ASP.net, but now I would like to do a connection from SQ Server.

    Here is the line from the web.config
    <add name="OracleBLMS" connectionString="Dsn=OracleDev1;uid=<username>;pwd=<password>"
             providerName="System.Data.Odbc" />

    What should my sp_addlinkedserver and sp_addlinkedsrvlogin look like?

    I have tried a few different things but no success.

    Thanks,
    Scott

    Wednesday, February 25, 2009 4:36 PM

Answers

  • Ok, I got it solved, or at least a good workaround.

    Everything works fine if I use openquery.

    so instead of
    select * from linked_server..schema.table_name

    I use
    select * from openquery(linked_server, 'select * from schema.table')

    This works with both the OLEDB and the ODBC connection.

    • Marked as answer by scottcmetzger Wednesday, March 18, 2009 4:57 PM
    Wednesday, March 18, 2009 4:57 PM

All replies

  • Hi,

     

    The following KB article presents how to set up a linked server to an Oracle database in SQL Server 2000.

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

     

    If you have any more questions, please let me know.

     

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, February 27, 2009 8:07 AM
  • Thanks, I read that article.  Its well written and was helpful but only deals with OLE DB


    I am at the point where I need specific information about what parameters I should be passing to sp_addlinkedserver when using ODBC.

    If I go through the SQL Enterprise Manager GUI I don't even get an option for an ODBC Provider, what gives?

    Thanks,
    Scott


    Monday, March 2, 2009 7:03 PM
  • Hi Scott,

    In the KB article I mentioned, the steps for setting up a linked server to Oracle support both Microsoft OLE DB Provider for Oracle and Microsoft ODBC Driver for Oracle.  The following syntax is for sp_addlinkedserver:
    sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
         [ , [ @provider= ] 'provider_name' ]
         [ , [ @datasrc=] originalAttribute="src" originalPath="]" originalAttribute="src" originalPath="]" originalAttribute="src" originalPath="]" 'data_source' ]
         [ , [ @location= ] 'location' ]
         [ , [ @provstr= ] 'provider_string' ]
         [ , [ @catalog= ] 'catalog' ]

    If you use Microsoft ODBC Driver for Oracle, you can use the @datasrc parameter to specify a DSN name. However, you could create a DSN to the Oracle server with the Oracle ODBC Driver uing the ODBC Administrator. The following example creates a linked server that uses the ODBC Driver for Oracle. 
        EXEC sp_addlinkedserver 
           @server = 'server_name', 
           @srvproduct = '',
           @provider = 'MSDASQL', 
           @datasrc='DSN'
        GO

    For a DSN-less connection, the provider string is supplied through the @provstr parameter.
        EXEC sp_addlinkedserver 
           @server = 'server_name', 
           @srvproduct = '',
           @provider = 'MSDASQL', 
           @provstr= 'provider_string'
        GO


    If you have any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, March 3, 2009 2:58 AM

  • Ok, thanks.

    I got the connection working two different ways.  But I am still having problems...
    1) OleDB

    sp_addlinkedserver 'RIIDHR_ALTESS_DEV1', 'Oracle', 'MSDAORA', 'RIIDHR_ALTESS_DEV1'


    But when I do a select on my table of interest I get the following error...
    OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time.

    I read through http://support.microsoft.com/kb/251238/
    so, I dropped the table and re-created it, so there have been no alter actions done on the table.

    But I still get the same error when I run my query.

    2) ODBC via DSN
    EXEC sp_addlinkedserver
           @server = 'test2',
           @srvproduct = '',
           @provider = 'MSDASQL',
           @datasrc='OracleDev1'

    My select statement runs without error, however, it does not return any data.  Yes, I double checked and the table has several thousand rows of data.

    Any ideas?

    Thanks,
    Scott

    Tuesday, March 3, 2009 9:37 PM
  • Ok, I got the ODBC connection to work.

    However I am still getting the error with the OLEDB connection.  There are no views on the table, no alter was done on the table

    How can I check the following....
    IDBSchemaRowset::GetRowset for DBSCHEMA_COLUMNS returns IS_NULLABLE as TRUE.

    IColumnsInfo::GetColumnInfo on the rowset returns DBCOLUMNFLAGS_ISNULL as FALSE.

    Do I need to write a program that calls those functions directly, or can I issue those commands somewhere in one of the SQL Server tool?

    Any ideas?

    Thanks,
    Scott
    Thursday, March 5, 2009 1:48 PM
  • bump

    Anyone?

    Should I post this in another forum?  If so, which one?

    Thanks,
    Scott
    Tuesday, March 17, 2009 2:00 PM
  • You may need to write a package in oracle that calls those.
    Tuesday, March 17, 2009 5:03 PM
  • Ok, I got it solved, or at least a good workaround.

    Everything works fine if I use openquery.

    so instead of
    select * from linked_server..schema.table_name

    I use
    select * from openquery(linked_server, 'select * from schema.table')

    This works with both the OLEDB and the ODBC connection.

    • Marked as answer by scottcmetzger Wednesday, March 18, 2009 4:57 PM
    Wednesday, March 18, 2009 4:57 PM