linked server to an Oracle database

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

Answer 1

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***

Answer 2

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

Answer 3

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***

Answer 4

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

Answer 5

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

Answer 6

bump

Anyone?

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

Thanks,
Scott

Answer 7

You may need to write a package in oracle that calls those.

Answer 8

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.