Problem with Linked Server

I am running a linked server "SGSC" from SQL 2k to Oracle 9i

EXEC sp_addlinkedserver @.server='SGSC',
@.provider='MSDAORA',
@.srvproduct = 'Oracle',
@.datasrc='SGSC'

EXEC sp_addlinkedsrvlogin
@.rmtsrvname ='SGSC',
@.useself ='false',
@.locallogin =NULL,
@.rmtuser = 'bisadmin',
@.rmtpassword = 'bisadmin'

When I am trying to execute the following query

SELECT * INTO GL_BALANCES FROM OPENQUERY(SGSC,'SELECT * FROM GL_BALANCES')

I get this error

<eb1>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.

State:01000,Native:7300,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]

State:37000,Native:7399,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]</eb1>

Can somebody help me out?

Assuming that your Oracle client is installed correctly on your SQL Server instance, I think you have a problem with the OPENQUERY statement that being passed to the Oracle server. The table name should be fully qualified with the catalog and schema names, like:

SELECT * INTO GL_BALANCES FROM OPENQUERY(SGSC,'SELECT * FROM THECATALOGNAME.THESCHEMANAME.GL_BALANCES')

or

SELECT * INTO GL_BALANCES FROM SGSC.THECATALOGNAME.THESCHEMANAME.GL_BALANCES

The catalog and schema names should be visible from within Enterprise Manager when you browse the linked server's database tables.

Mark

|||Hi Mark,

Thanks for replying.
I tried executing the query using catalog and schema names but still it gives the same error|||I assume that the query is valid? (Run SQL Plus on the box where SQL Server resides and login using bisadmin/bisadmin@.SGSC.)
Have you gotten any linked queries to run on this server using OPENQUERY? Can you run 'SELECT * FROM dual'? If you get errors doing this, are the error codes the same?
When logging into SQL Server, are you using Windows Authentication or a SQL Server login?|||Make sure that SQL Server server appears before oracle in the server path statement. The following article helped me tremendousely http://windowsitpro.com/articles/print.cfm?articleid=22264|||Hi All,

Here is my system:

Windows 2003 Server
Oracle 9i client client (9.2.0.1.0)
SQL Server 2000 (SP 3)

Here is my story:

I've setup my Linked Server according to this article http://support.microsoft.com/kb/280106/
and built the test environment according to this article : http://objectsharp.com/Blogs/matt/archive/2005/06/13/2221.aspx

When I issued this command

begin tran

exec CallOracleProcTest

rollback tran

i get this error :

Error 7391: The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]

Error 7391: The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]

Can anybody help me ?|||Im getting the same message as you do. I've checked that the DTC is running and i still get the error. Any suggestions?|||Hi, Can anyone tell me if there was ever a sucessful resolution/conclusion to this?

We've been going round in circles trying to link a Sybase db into SQLServer hosted on w W2K3 platform.

Just looking at the other linked articles now but I'd like to hear if anyone found out what was going on or if indeed why?

Thanks

Paul.