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.sql
