Connectivity fails to remote Oracle linked server

I have linked an oracle remote database from MS SQL server 2000, connecting
an Oracle 8.1.7 database. The linked worked fine for a few days but fails
now with a message 'Error 7399:OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE DB Provider 'MSDAORA' DBInitialize::Initialized
returned 0x80004005. ]. The version of MSDAORA.DLL being used is 4.71. Any
ideas where the problem might be? I can connect to Oracle database from SQL
server via sql+.That's a generic error. Try turning on trace flag 7300 or
us SQL Profiler to capture the OLEDB Errors event. This
should give you to get more information about the error.
Additionally, you'll want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Sun, 18 Jul 2004 21:59:55 GMT, "Javed"
<purdaysee@.hotmail.com> wrote:

>I have linked an oracle remote database from MS SQL server 2000, connecting
>an Oracle 8.1.7 database. The linked worked fine for a few days but fails
>now with a message 'Error 7399:OLE DB provider 'MSDAORA' reported an error.
>OLE DB error trace [OLE DB Provider 'MSDAORA' DBInitialize::Initialized
>returned 0x80004005. ]. The version of MSDAORA.DLL being used is 4.71. Any
>ideas where the problem might be? I can connect to Oracle database from SQL
>server via sql+.
>|||I have the same setup. Friday 07/30/2004 the linked server started to fail w
ith the same message you are getting. We are looking into some OS security p
atches that were installed as the possible problem. Specifically MS04-020 an
d MS04-024. No resolution y
et.
Have you found the problem?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Connectivity fails to remote Oracle linked server

I have linked an oracle remote database from MS SQL server 2000, connecting
an Oracle 8.1.7 database. The linked worked fine for a few days but fails
now with a message 'Error 7399:OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE DB Provider 'MSDAORA' DBInitialize::Initialized
returned 0x80004005. ]. The version of MSDAORA.DLL being used is 4.71. Any
ideas where the problem might be? I can connect to Oracle database from SQL
server via sql+.
That's a generic error. Try turning on trace flag 7300 or
us SQL Profiler to capture the OLEDB Errors event. This
should give you to get more information about the error.
Additionally, you'll want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Sun, 18 Jul 2004 21:59:55 GMT, "Javed"
<purdaysee@.hotmail.com> wrote:

>I have linked an oracle remote database from MS SQL server 2000, connecting
>an Oracle 8.1.7 database. The linked worked fine for a few days but fails
>now with a message 'Error 7399:OLE DB provider 'MSDAORA' reported an error.
>OLE DB error trace [OLE DB Provider 'MSDAORA' DBInitialize::Initialized
>returned 0x80004005. ]. The version of MSDAORA.DLL being used is 4.71. Any
>ideas where the problem might be? I can connect to Oracle database from SQL
>server via sql+.
>
|||I have the same setup. Friday 07/30/2004 the linked server started to fail with the same message you are getting. We are looking into some OS security patches that were installed as the possible problem. Specifically MS04-020 and MS04-024. No resolution y
et.
Have you found the problem?
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Connections...

Dear All,
SQL Server 2000, Service Pack 3a
We have a problem with locking where as soon as something deadlocks the
application fails.
There are two solutions that spring to mind, either totally re-writing the
Application or setting the SET LOCK_TIMEOUT command.
Now currently we cannot do the re-write as the developers don't have time (I
was out voted) so we are looking to the Set Lock_Timeout, but here is the
problem.
The Connections used are set by JBOSS and our developers cannot find a way
of setting the SET LOCK_TIMEOUT to the connection through JBOSS.
What I was wondering then was is there a way through SQL, i.e. Whenever a
connection is created it automatically does a SET LOCK_TIMEOUT ?
If not could someone go through how a connection is actually created?, so I
can attempt to put a trigger somewhere.
I would totally agree that this is a 'hack' fix and does not solve the
underlying problem but they want a quick fix :(
JJulie,
changing the Lock timeout won't solve you're problem. The Lock timeout
only defines how long a query wait for a resource until a exlusive lock
is released. By default a query waits indefinetely until the lock is
released.
Deadlocks are different. SQL Server detects if a deadlock occurs and
the kills one process, the deadlock victim. This happens very fast, so
the time the resource is locked would be shorter than your timeout.
You need to catch the error 1205 and the handle it in your application.
I don't think there's a alternative for that.
Markus

Connections...

Dear All,
SQL Server 2000, Service Pack 3a
We have a problem with locking where as soon as something deadlocks the
application fails.
There are two solutions that spring to mind, either totally re-writing the
Application or setting the SET LOCK_TIMEOUT command.
Now currently we cannot do the re-write as the developers don't have time (I
was out voted) so we are looking to the Set Lock_Timeout, but here is the
problem.
The Connections used are set by JBOSS and our developers cannot find a way
of setting the SET LOCK_TIMEOUT to the connection through JBOSS.
What I was wondering then was is there a way through SQL, i.e. Whenever a
connection is created it automatically does a SET LOCK_TIMEOUT ?
If not could someone go through how a connection is actually created?, so I
can attempt to put a trigger somewhere.
I would totally agree that this is a 'hack' fix and does not solve the
underlying problem but they want a quick fix
JJulie,
changing the Lock timeout won't solve you're problem. The Lock timeout
only defines how long a query wait for a resource until a exlusive lock
is released. By default a query waits indefinetely until the lock is
released.
Deadlocks are different. SQL Server detects if a deadlock occurs and
the kills one process, the deadlock victim. This happens very fast, so
the time the resource is locked would be shorter than your timeout.
You need to catch the error 1205 and the handle it in your application.
I don't think there's a alternative for that.
Markus

ConnectionOpen(Connect()) in SQL server 2000

hi,
i am new to SQL Server 2000 n trying to set up a Server in Windows 200
pro,
but whenever i try to Register the server it fails n gives the below
error,
ConnectionOpen(Connect()) faIled.
plz if some one can rectify this issue,
thanks in advance,
mandeepFirstly check if your SQL Server services are running; If yes then
proceed
Start-->programs --> Microsoft SQL Server --> Client Network Utility
Check to see if TCP/IP and NamedPipes are enabled only check if Shared
Memory is checked, if its not checked, check it

Connection.Mode=adModeRead fails?

Hi,
vb6+mssql2000 i want to disable updates by set cn.mode=adModeRead. inspite
of this, i can do modifications without error... any tip? what should i
check?
Hi,
It could be that provider does not support this mode. What is your
connection string?
Val Mazur
Microsoft MVP
"SRINGER Zoltn" <kecskemetisrac@.remove.it.freemail.hu> wrote in message
news:OIj6BsMoEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Hi,
> vb6+mssql2000 i want to disable updates by set cn.mode=adModeRead.
> inspite
> of this, i can do modifications without error... any tip? what should i
> check?
>
|||i dont remember exactly, but nothing strange:
provider=sqloledb, login=sa..
"Val Mazur" <group51a@.hotmail.com> az albbiakat rta a kvetkezo zenetben
news:uvYHPaRoEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It could be that provider does not support this mode. What is your
> connection string?
> --
> Val Mazur
> Microsoft MVP
>
> "SRINGER Zoltn" <kecskemetisrac@.remove.it.freemail.hu> wrote in message
> news:OIj6BsMoEHA.3876@.TK2MSFTNGP15.phx.gbl...
>
|||Are you sure about connection string? I believe OLEDB for SQL Server
supports it
Val Mazur
Microsoft MVP
"SRINGER Zoltn" <kecskemetisrac@.remove.it.freemail.hu> wrote in message
news:euRGzTZoEHA.644@.tk2msftngp13.phx.gbl...
>i dont remember exactly, but nothing strange:
> provider=sqloledb, login=sa..
>
> "Val Mazur" <group51a@.hotmail.com> az albbiakat rta a kvetkezo
> zenetben
> news:uvYHPaRoEHA.3864@.TK2MSFTNGP10.phx.gbl...
>
|||hi Val,
"Val Mazur" <group51a@.hotmail.com> ha scritto nel messaggio
news:%23Pk9yndoEHA.516@.TK2MSFTNGP09.phx.gbl
> Are you sure about connection string? I believe OLEDB for SQL Server
> supports it
>
actually I tried myself with VB6, OLE DB provider, ADO 2.6 and had the same
result indeed..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Connection to Oracle fails when using SQLNET.AUTHENTICATION_SERVICES = (NTS)

I'll say up front that this is going to be a long post because I plan on explaining my situation and what I've tried up front. It requires some explanation of how our application is secured since that appears to be interfering with SSIS being able to connect.

First the background scenario.

My company writes software that can use MS SQL or Oracle as the back end. I've begun work on an initiative to extract data to a star schema prior to purge in an attempt to improve reporting and analysis available to our customers. Initial plan is to use MS SQL Integration/Analysis/Reporting services for this. Even for our Oracle customers.

The problem symtoms.

I'm attempting to get a connection to Oracle with either an OLE DB or .Net provider. I've tried all available and get a ORA-12638: Credential retrieval failed error on all of them. I validate that a development username and password work using both SQL*Plus and Toad so troubleshooting begins. After some research I find that if I change my line for SQLNET.AUTHENTICATION_SERVICES in my sqlnet.ora file on the server from NTS to NONE that I can now get a connection with the SSIS Connection Manager.

The problem is, our application is secure and having this value set to NONE will never happen in production. I don't use it in dev or qa either. I also found that once I set this to none I could no longer connect with integrated security (EXTERNAL in Oracle speak) with Toad.

How our application is secured and why.

When setting up our application with Oracle as the back end we create only two users. One is the schema and owns all objects. It can't even log into the server though. The other is an external user. This users only rights are to log on and membership in a role. The role can execute stored procedures and nothing more. The application server services for our software run under the username of the external user in Oracle. That way we can use integrated security and no usernames or passwords are hard coded anywhere. Using an external user allows us to have our Windows application server work correctly with Oracle regardless of the OS that is hosting Oracle. This has made it easy on my developers since they just code for using integrated security and we have nearly identical data abstraction layers for both MS SQL and Oracle.

I've tried using the Integrated Security = True option in the .Net provider and still get the same error. I've tried passing / as the username and get the same error. I have a regular Oracle username/password in dev and qa environments that I provide the developers. It has more rights that the external user the services runs under so they can investigate what is happening behind the scenes. It works with Toad and SQL*Plus. When I use it in SSIS CM I get the same error message.

How do you use integrated security with Oracle from SSIS? I don't want to have to tell my boss that SSIS won't work for our Oracle customers.

Which Oracle driver are you using? The Oracle OLE driver, or the Microsoft OLE DB for Oracle?|||I've tried the Microsoft OLE DB Provider for Oracle, the Oracle Provider for OLE DB, and the .Net OracleClient Data Provider. The .Net driver is the only one that has an Integrated Security setting in the Connection Manager GUI. Normally with Oracle if I want to use integrated security I pass in a / for the User ID. If connecting with something like SQL*Plus I would use something like /@.TNSName or possibly /@.TNSName as sysdba if the user is a sysdba on the Oracle instance.

Connection to MSDE fails

I'm troubleshooting a connection to an MSDE install. I tried to register
the MSDE install on the server's EM. The register server wizard shows the
laptop's MSDE install but it refuses to connect.
-No software firewall is running on the laptop
-I added TCP/IP using port 1433 to the MSDE install
-The MSDE server shows up when I browse for SQL servers on the network
-I've tried integrated security and SQL authentication but neither work.
-I can connect to the MSDE install from the laptop using both authentication
types
Any ideas why EM can browse the MSDE server but not connect?
I'm using MSDE 2000 sp3 and SQL Server 2000 sp3
ThanksIf you come up with an answer, I would appreciate notice. I am having
the same problem.
If I connect an .adp directly it is fine. Query Analyzer is fine.
I cannot connect to either remote SQL Server DB I have. It is very
frustrating.
Thank you. Sorry I can't help you either.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Check the SQL Errorlogs for the MSDE instance. See what protocols it's
listening on.
You should see Shared Memory, Named Pipes and TCP.
Also, the default security is Windows Authentication.
Next try connecting using either ISQL.exe or a Test ODBC DSN. You need the
OS error to tshoot the problem.
Example:
OS Error 53 == Network Name not found. (Named Pipe connection without
Netbios Name Resolution)
OS Error 10060 == TCP Connection Timeout
OS Error 10061 == TCP connection Refused. Possibly the client is
connecting on the wrong socket. Or the server isn't listening on tcp.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connection to MSDE fails

I'm troubleshooting a connection to an MSDE install. I tried to register
the MSDE install on the server's EM. The register server wizard shows the
laptop's MSDE install but it refuses to connect.
-No software firewall is running on the laptop
-I added TCP/IP using port 1433 to the MSDE install
-The MSDE server shows up when I browse for SQL servers on the network
-I've tried integrated security and SQL authentication but neither work.
-I can connect to the MSDE install from the laptop using both authentication
types
Any ideas why EM can browse the MSDE server but not connect?
I'm using MSDE 2000 sp3 and SQL Server 2000 sp3
Thanks
If you come up with an answer, I would appreciate notice. I am having
the same problem.
If I connect an .adp directly it is fine. Query Analyzer is fine.
I cannot connect to either remote SQL Server DB I have. It is very
frustrating.
Thank you. Sorry I can't help you either.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Check the SQL Errorlogs for the MSDE instance. See what protocols it's
listening on.
You should see Shared Memory, Named Pipes and TCP.
Also, the default security is Windows Authentication.
Next try connecting using either ISQL.exe or a Test ODBC DSN. You need the
OS error to tshoot the problem.
Example:
OS Error 53 == Network Name not found. (Named Pipe connection without
Netbios Name Resolution)
OS Error 10060 == TCP Connection Timeout
OS Error 10061 == TCP connection Refused. Possibly the client is
connecting on the wrong socket. Or the server isn't listening on tcp.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connection to Lan fails and locks server

Morning,
We have two nodes in a sql cluster. The "Lan" connection on one of the
servers fails at different times and I can't track down the problem and I am
not sure where to start.
I have changed NIC's Cables, Switches and still no luck..
Any suggestions where to start would be appreciated.
Thanks
Firmware? BIOS? Drivers? Called the vendors?
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"James" <James@.discussions.microsoft.com> wrote in message
news:C32C79C1-AE2F-4F80-B425-F038C789D0F8@.microsoft.com...
> Morning,
> We have two nodes in a sql cluster. The "Lan" connection on one of the
> servers fails at different times and I can't track down the problem and I
> am
> not sure where to start.
> I have changed NIC's Cables, Switches and still no luck..
> Any suggestions where to start would be appreciated.
> Thanks
|||Actually we had a DELL rep come out and set this up because we did not want
to mess with the SAN LUN's. He couldn't figure it out either. I will take a
look at the BIOS. We are running a Win 2003 with sql 2000 cluster.
I noticed earlier that Network DTC was not installed on both servers so I
installed that see if maybe that will make a difference.
"Rodney R. Fournier [MVP]" wrote:

> Firmware? BIOS? Drivers? Called the vendors?
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "James" <James@.discussions.microsoft.com> wrote in message
> news:C32C79C1-AE2F-4F80-B425-F038C789D0F8@.microsoft.com...
>
>
|||"James" <James@.discussions.microsoft.com> wrote in message
news:9E080491-54A2-43AF-9E79-45B388030ACE@.microsoft.com...
> Actually we had a DELL rep come out and set this up because we did not
want
> to mess with the SAN LUN's. He couldn't figure it out either. I will
take a
> look at the BIOS. We are running a Win 2003 with sql 2000 cluster.
>
To ask the obvious... SP3a installed?
[vbcol=seagreen]
> I noticed earlier that Network DTC was not installed on both servers so I
> installed that see if maybe that will make a difference.
> "Rodney R. Fournier [MVP]" wrote:
the[vbcol=seagreen]
and I[vbcol=seagreen]
|||SP3 A is installed. Server failed again after 3 day. Network cards pass all
test? I will post some logs see if anyone has seen it before.
"Greg D. Moore (Strider)" wrote:

> "James" <James@.discussions.microsoft.com> wrote in message
> news:9E080491-54A2-43AF-9E79-45B388030ACE@.microsoft.com...
> want
> take a
> To ask the obvious... SP3a installed?
>
> the
> and I
>
>
|||Ok Here is the Cluster Log as soon as it fails I cannot figure this out. We
have had this problem for 3 weeks now, the amount of time it takes to fail
varies. Any help would be appreciated.
000003c0.00000508::2004/12/30-14:53:44.063 INFO [NM] Reporting connectivity
to leader for network 4acd10cf-f965-42a4-b597-14d61d286b4c.
000003c0.00000508::2004/12/30-14:53:44.063 INFO [NM] Connectivity report
completed successfully for network 4acd10cf-f965-42a4-b597-14d61d286b4c.
000003c0.00000508::2004/12/30-14:53:44.063 INFO [NM] Connectivity report
worker thread finished.
000003c0.00000804::2004/12/30-14:53:46.531 INFO [GUM] s_GumUpdateNode:
dispatching seq 8884type 2 context 15
000003c0.00000804::2004/12/30-14:53:46.531 INFO [NM] Received update to set
state for network 4acd10cf-f965-42a4-b597-14d61d286b4c.
000003c0.00000804::2004/12/30-14:53:46.547 WARN [NM] Interface
8a0a2f09-80e0-46a1-ae1f-663f765f414b is unreachable (node: Server1, network:
LAN).
000003c0.00000804::2004/12/30-14:53:46.547 WARN [NM] Interface
09b7cff6-2020-43ba-bb7e-23f36d09f7c4 is unreachable (node: Server2, network:
LAN).
000003c0.00000804::2004/12/30-14:53:46.547 WARN [NM] Network
4acd10cf-f965-42a4-b597-14d61d286b4c (LAN) is down.
000003c0.00000804::2004/12/30-14:53:46.547 INFO [GUM] s_GumUpdateNode:
completed update seq 8884type 2 context 15
000003c0.00000804::2004/12/30-14:53:50.405 INFO [NM] Received request to get
online address enum for interface 09b7cff6-2020-43ba-bb7e-23f36d09f7c4.
000003c0.00000804::2004/12/30-14:53:50.499 INFO [NM] Found address
10.40.0.91 for interface 09b7cff6-2020-43ba-bb7e-23f36d09f7c4.
000003c0.00000804::2004/12/30-14:53:50.499 INFO [NM] Received request to get
ping address enum for interface 09b7cff6-2020-43ba-bb7e-23f36d09f7c4.
000003c0.00000804::2004/12/30-14:53:50.515 INFO [NM] Received request to
ping targets for interface 09b7cff6-2020-43ba-bb7e-23f36d09f7c4.
000003c0.00000804::2004/12/30-14:53:50.515 INFO [NM] Pinging targets for
interface 09b7cff6-2020-43ba-bb7e-23f36d09f7c4.
000003c0.00000804::2004/12/30-14:53:50.515 INFO [NM] Pinging host 10.40.0.1
000003c0.00000804::2004/12/30-14:53:54.139 INFO [NM] Ping of host 10.40.0.1
failed.
000003c0.00000804::2004/12/30-14:53:54.139 INFO [NM] Pinging host 10.40.0.182
000003c0.00000804::2004/12/30-14:53:58.138 INFO [NM] Ping of host
10.40.0.182 failed.
000003c0.00000804::2004/12/30-14:53:58.138 INFO [NM] Pinging host 10.40.0.2
000003c0.00000804::2004/12/30-14:54:02.138 INFO [NM] Ping of host 10.40.0.2
failed.
000003c0.00000804::2004/12/30-14:54:02.138 INFO [NM] Pinging host 10.40.0.7
000003c0.00000804::2004/12/30-14:54:06.137 INFO [NM] Ping of host 10.40.0.7
failed.
000003c0.00000804::2004/12/30-14:54:06.137 INFO [NM] Pinging host 10.40.0.39
000003c0.00000804::2004/12/30-14:54:10.136 INFO [NM] Ping of host 10.40.0.39
failed.
000003c0.00000804::2004/12/30-14:54:10.136 INFO [NM] Finished pinging
targets for interface 09b7cff6-2020-43ba-bb7e-23f36d09f7c4.
000003c0.00000688::2004/12/30-14:54:10.136 INFO [GUM] s_GumUpdateNode:
dispatching seq 8885type 2 context 15
000003c0.00000688::2004/12/30-14:54:10.136 INFO [NM] Received update to set
state for network 4acd10cf-f965-42a4-b597-14d61d286b4c.
000003c0.00000688::2004/12/30-14:54:10.136 INFO [NM] Interface
8a0a2f09-80e0-46a1-ae1f-663f765f414b is up (node: Server1, network: LAN).
000003c0.00000688::2004/12/30-14:54:10.136 WARN [NM] Interface
09b7cff6-2020-43ba-bb7e-23f36d09f7c4 failed (node: Server2, network: LAN).
000003c0.00000688::2004/12/30-14:54:10.136 WARN [NM] Network
4acd10cf-f965-42a4-b597-14d61d286b4c (LAN) is up.
000003c0.00000688::2004/12/30-14:54:10.136 INFO [GUM] s_GumUpdateNode:
completed update seq 8885type 2 context 15
"James" wrote:

> Morning,
> We have two nodes in a sql cluster. The "Lan" connection on one of the
> servers fails at different times and I can't track down the problem and I am
> not sure where to start.
> I have changed NIC's Cables, Switches and still no luck..
> Any suggestions where to start would be appreciated.
> Thanks

Connection Problems to Oracle Database

Hi,

I have a package connecting to oracle database ,this was developed on 32 bit Windows OS now i moved the same to 64 bit OS and it fails to connect to oracle database.

I have created system DSN and tested it was successfull.

In my package i am using ado.net connection object ,I am using ODBC data provider.I select the Data source in the drop down .provide user name and password and when i hit on test connection it fails with the following message

Test connection failed because of error in initilizing the provider error[08004]

[oracle][odbc][ora]ORA 12154 : TNS could not resolve the connect identifier specified

[Error]IM006[Microsoft][ODBC driver manager] drivers SQL setconnectattr failed

Any help on this will be appreciated

Note:- even though the OS is 64 bit i am using 32 bit ODBC as BI studio can only access 32 bit odbc

32-bit ODBC drivers won't run with 64-bit SSIS. You either need a 64-bit ODBC driver for Oracle (not sure if one exists) or run the 32-bit version of SSIS on the 64-bit box.|||Initially i had installed 64 bit oracle drivers and had created sytem dsn in 64 bit odbc but when i tried accesing the DSN(from drop down in connection object) i created in ado.net connection object i was not able to do so , as using the BI studio you can only access 32 bit odbc for which i had to install 32 bit oracle drivers after creating the dsn in 32 bit odbc i was able to access the DSN but when i did the test connection it failed.with above error message.