Link Oracle 10g with SQL Server problem

35 pts.
Tags:
Error 7399
Oracle 10g
Oracle error messages
OraOLEDB.Oracle
SQL Server errors
I have created a link between our Oracle DB and SQL server db It was successfully created and I was able to select data from oracle table using SQL Server. Then I have created a view in SQL Server using this link to read from Oracle, the view was created:
create view dah_test as (
SELECT count(*) as reg, semester FROM OPENQUERY( dah,'SELECT * from si_stu_major') group by semester
)
but when i tried to open the content it gives me the following error: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'OraOLEDB.Oracle' reported an error. [OLE/DB provider returned message: ] OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetNextRows returned 0x80004005: ].
ASKED: August 10, 2008  8:00 AM
UPDATED: October 23, 2013  6:54 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

What happens if you rewrite the view to not use the OPEN Query?

CREATE VIEW dah_test AS
SELECT semester, count(*) as reg
FROM dah..Schema.si_stu_major
GROUP BY semester
GO

(You will need to put the correct Oracle schema in place for this to work.)

With the way that you have it written then entire table will be transferred from the Oracle server to the SQL Server, then the SQL Server will do the GROUP BY and COUNT operation. This way the Oracle server does the work, and simply returns you the information that you need.

It could simply be that you need to include the Schema in your OPEN Query as well.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    65,480 pointsBadges:
    report
  • Kmadani
    i have run the sql statment, and i have placed the correct schema hich is the database owner schema, but it gives me the followig error OLE DB provider 'OraOLEDB.Oracle' returned an invalid schema definition. OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid schema definition.]. I'm sure that i have used the correct schma. I have checked it from the database EM
    35 pointsBadges:
    report
  • Miker
    Have you only created a link from SQLserver to Oracle? You should also setup a link going the other way try using the transparent gateway TG4MSQL
    10 pointsBadges:
    report
  • Kmadani
    what i need is to allow SQL Server to read from Oracle, do i need to create database link from Oracle to Sql. if yes how i can implement that.
    35 pointsBadges:
    report
  • Denny Cherry
    I've posted an additional response on the new thread http://itknowledgeexchange.techtarget.com/itanswers/sql-integration-with-oracle10g
    65,480 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following