sql server 2005 link server for oracle 10g

Question

Post reply

sql server 2005 link server for oracle 10g

samiam914

SSCommitted

Points: 1886
More actions
April 14, 2008 at 8:06 pm

#120957

I am brand new to SQL server 2005.
Did the following:
1) Installed Oracle client 10g, restarted the server machine
checked using oracle enterprise manager, it is good.
2) changed - now
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI is correct:
OracleOciLib = oci.dll
OracleSqlLib = orasql10.dll
OracleXaLib = oraclient10.dll
restarted the machine
(Before I changed old values were
OracleOciLib = oci.dll
OracleSqlLib =SQLLIB80.dll
OracleXaLib = xa80.dll)
3)
EXEC sp_addlinkedserver 'DTEST10Link', 'Oracle', 'OraOLEDB.Oracle', 'DTEST10'
EXEC sp_addlinkedsrvlogin 'DTEST10Link', 'FALSE', NULL, 'userid', 'pswd'
executed without error, no problem.
4) select count(*) from DTEST10Link..coll18_test.terms
get the following errors:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "DTEST10Link" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "DTEST10Link".
When I run "select count(*) from coll18_test.terms" in SQL*Plus it works.
I believe I have MDAC 2.8.
Any help appreciated.
Sam

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 1

select count(*) from DTEST10Link..coll18_test.terms

... must be...

select count(*) from DTEST10LINK..COLL18_TEST.TERMS

The driver for Oracle isn't so hot... it wants to see everything in the FROM clause as all UPPER case.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

samiam914 SSCommitted Points: 1886 More actions · Answer 2

Jeff,

I put it in caps the way you wrote it and ran it, same error. I have a feeling, my link server is not established properly, I would assume that I should be able to see all tables, views, stored procedures etc when I expand the linked server, I do not see them. When I click on '+' sign next to link server, I only see catalogs, when I expand that I see System catalogs and default. When I expand default I see folder for tables and views, but nothing in them.

Thnx

Sam

samiam914 SSCommitted Points: 1886 More actions · Answer 3

Jeff,

I created link sever using "Microsoft OLE DB Provider for Oracle", it works, it does not with "Oracle Provider for OLE DB".

You are 100% right what follows after "from" must be uppercase, I would have never known that in a million years, silly driver.

I can see views and tables, but do not see Oracle stored procedures, hopefully I can run them using the link server. I have to search here for some examples to run them. I have a Oracle stored procedure that returns a REF cursor and takes several input parameters.

Thnx a million

Sam

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 4

samiam914 (4/15/2008)
I can see views and tables, but do not see Oracle stored procedures, hopefully I can run them using the link server. I have to search here for some examples to run them. I have a Oracle stored procedure that returns a REF cursor and takes several input parameters.

I've heard some people say that thy've been able to run Oracle stored procs from SQL Server, but I've never tried it nor verified it...

Instead of a stored proc with a REF cursor, which always seemed like a PITA to me, why not just create a view that does the same thing as the proc... with Sub-Query Refactoring (Oracle name for CTE's), you should be able to do some remarkable stuff.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

magasvs SSCertifiable Points: 7660 More actions · Answer 5

Verify that you enabled this Oracle provider for your SQL instance:

- go to the SQL Management Studio -> Server Objects -> Linked Servers -> Providers

- double click on the OraOLEDB.Oracle provider and make sure that "Allow inprocess" is enabled for this provider.

samiam914 SSCommitted Points: 1886 More actions · Answer 6

Jeff & Sveg,

Jeff:

I will look into your suggestion to see if it is possible, Oracle stored procedure that returns a REF cursor has pl/sql code not just queries (I guess that would be equivalent to T-sql code in sql server).

From up above posting: With regard to capitalizing any thing after "from" in sql, it works without capitalizing using the following method. I am sure you know this already, I am indicating here for the benefit of other Newbies like me.

SELECT *

FROM OPENQUERY(DPROD10, 'select terms_id from coll_production.terms')

GO

SVeg:

Thanks for your suggestion, that was it, it works now with Oracle provided OraOLEDB.Oracle.

When I write queries using query analyzer, would I be able to use linked server tables to join with sql server table in Query analyzer. I assuming by writing sql I can, I have not tried yet. In MS Access you can join liked tables/objects with MS Access tables, with out a problem. I am sure it can be done here also, I just have'nt tried it yet.

Thank you both