Help! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?
Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:

> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

Help! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:
> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

Help! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:

> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

Help with update query linking 3 tables

I am looking for some assistance with an update query that needs to link 3
tables:

This query ran and reported over 230,000 records affected but did not change
the field I wanted changed, not sure what it did.
I did notice that the "name" in "GM_NAMES.name" was colored blue in Query
Analyzer. Is it bad to name a column "name"?

UPDATE ABSENCES
set CustomerContactID = cicntp.cnt_id
from absences, cicntp
where (SELECT cicntp.cnt_l_name
FROM cicntp INNER JOIN
gm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOIN
Absences ON cicntp.cnt_id = Absences.CustomerContactID)

Next I tried this query which is still running after 75 minutes (on a
laptop)

update absences
set CustomerContactID = cicntp.cnt_id
from absences, cicntp, gm_names
where gm_names.name= cicntp.cnt_l_name

As you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.
Absences.CustomerContactID is what I need updated, when finished it should
match CICNTP.cnt_id
GM_NAMES is a temp table and matches records in CICNTP.cnt_l_name

Can some of you school this newbie on the best way to do this?
Thanks a bunch!On Fri, 15 Apr 2005 19:54:56 GMT, rdraider wrote:

>I am looking for some assistance with an update query that needs to link 3
>tables:
>This query ran and reported over 230,000 records affected but did not change
>the field I wanted changed, not sure what it did.
>I did notice that the "name" in "GM_NAMES.name" was colored blue in Query
>Analyzer. Is it bad to name a column "name"?
>UPDATE ABSENCES
>set CustomerContactID = cicntp.cnt_id
>from absences, cicntp
>where (SELECT cicntp.cnt_l_name
> FROM cicntp INNER JOIN
> gm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOIN
> Absences ON cicntp.cnt_id = Absences.CustomerContactID)

Hi rdraider,

I guess that you made a copy & paste error, since this query can never
have reported over 230,000 rows affected - it can only report "Incorrect
syntax near ')'".

>Next I tried this query which is still running after 75 minutes (on a
>laptop)
>update absences
>set CustomerContactID = cicntp.cnt_id
>from absences, cicntp, gm_names
>where gm_names.name= cicntp.cnt_l_name

Depending on the size of the tables, it'll probably run a whole lot
longer - and it likely won't result in the change you wish. Since you
didn't specify anything to link absences to either of the other tables,
this query will effectively:

1. Join cicnt and gm_names on the name column, as specified in the WHERE
clause; then
2. Update EACH row in absences with the value of EACH row in the result
of the join above. If that join yields a million rows, then each row in
absences will be updated a million times. And in the end, the cnt_id
value from whatever row happens to be processed last will end up being
in CustomerContactID for ALL absences!

>As you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.
>Absences.CustomerContactID is what I need updated, when finished it should
>match CICNTP.cnt_id
>GM_NAMES is a temp table and matches records in CICNTP.cnt_l_name
>Can some of you school this newbie on the best way to do this?
>Thanks a bunch!

Neither this narrative, nor any of the queries you posted does a very
good job at explaining what you need to get done. A much better way to
ask help in newsgroups is to post the table structure (as CREATE TABLE
statements), some rows of sample data (as INSERT statements) and the
expected output. More on this can be found at www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||1) Learn the Standard SQL UPDATE syntax, so you will not get Cartesian
explosions problems that will destroy your data integrity.

2) Avoid temp tables in favor of derived tables that the optimizer can
use.

3) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

4) Here is a wild guess. I would replace GM_names with a derived
table.

UPDATE Absenses
SET customer_contact_id
= (SELECT C1.cnt_id
FROM Cicntp AS C1, GM_names AS G1
WHERE C1.cnt_l_name = G1.name
AND C1.cnt_id = Absences.customer_contact_id)
WHERE EXISTS
(SELECT *
FROM Cicntp AS C1, GM_names AS G1
WHERE C1.cnt_l_name = G1.name
AND C1.cnt_id = Absences.customer_contact_id);

If the subquery expression returns no rows, you will get a NULL; If it
retursn more than one row, you will get a cardinality violation.|||On 15 Apr 2005 15:21:03 -0700, --CELKO-- wrote:

>4) Here is a wild guess. I would replace GM_names with a derived
>table.
>UPDATE Absenses
> SET customer_contact_id
> = (SELECT C1.cnt_id
> FROM Cicntp AS C1, GM_names AS G1
> WHERE C1.cnt_l_name = G1.name
> AND C1.cnt_id = Absences.customer_contact_id)
> WHERE EXISTS
> (SELECT *
> FROM Cicntp AS C1, GM_names AS G1
> WHERE C1.cnt_l_name = G1.name
> AND C1.cnt_id = Absences.customer_contact_id);

Hi Joe,

I was starting to post the exact same solution, but then I saw that this
version makes no sense at all - look at the way the subqueries are
joined to the table to be updated: the effect will be that
customer_contact_id will only be changed if it already has the correct
value. In other words: this UPDATE statement might affect some rows, but
it definitely won't change any data!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes. But this is what he wrote when both of us (who are pretty good
SQL guys) trie to understand it. It would be nice to have some DDL and
clear specs instead of bad code ...