LinkedServer Failure to connect Oracle 8.1.7

The Error Occured while connecting to oracle from
SQLServer 2000 in WINDOWS 2003, I have tried with MDAC
2.8, MDAC 2.7 and restarted the Server, nothing is
working. We are always getting the Same Error
Error 7399: OLE DB Provider 'MSDAORA' Reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
IDBInitialize::Initialize returned 0x80004005: ].
But i tried with one of the Windows 2000 machine, its
working fine, Please help...
Thanks in advance, NEED URgent HELP, Whole project gets
stopped because of THIS.
Thanks
Paul
I have the same problem.
If you find the solution, please post the message here.
Thanks.
Vlado
"Paul" <Paulr@.fieldpower.com> wrote in message
news:024501c49781$0fa9fb30$a501280a@.phx.gbl...
> The Error Occured while connecting to oracle from
> SQLServer 2000 in WINDOWS 2003, I have tried with MDAC
> 2.8, MDAC 2.7 and restarted the Server, nothing is
> working. We are always getting the Same Error
> Error 7399: OLE DB Provider 'MSDAORA' Reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80004005: ].
> But i tried with one of the Windows 2000 machine, its
> working fine, Please help...
> Thanks in advance, NEED URgent HELP, Whole project gets
> stopped because of THIS.
> Thanks
> Paul
|||Try turning on trace flag 7300 to get a more detailed error
message. In Query Analyzer, execute the following:
Dbcc traceon (7300,3604)
and then try executing a query against oracle.
-Sue
On Fri, 10 Sep 2004 14:56:44 -0700, "Paul"
<Paulr@.fieldpower.com> wrote:

>The Error Occured while connecting to oracle from
>SQLServer 2000 in WINDOWS 2003, I have tried with MDAC
>2.8, MDAC 2.7 and restarted the Server, nothing is
>working. We are always getting the Same Error
>Error 7399: OLE DB Provider 'MSDAORA' Reported an error.
>OLE DB error trace [OLE/DB Provider 'MSDAORA'
>IDBInitialize::Initialize returned 0x80004005: ].
>But i tried with one of the Windows 2000 machine, its
>working fine, Please help...
>Thanks in advance, NEED URgent HELP, Whole project gets
>stopped because of THIS.
>Thanks
>Paul
|||I am also having the same problem on a Windows 2003 box and can't find a
resolution ANYWHERE!!!!! Linked Oracle Server on Windows 2000 box has been
working great all along. Come on Microsoft - what is the story? I have
seen hundreds and hundred of posts on this issue with no known resolution.
This is a widely used functionality. Anyone out there overcome it??
"Paul" wrote:

> The Error Occured while connecting to oracle from
> SQLServer 2000 in WINDOWS 2003, I have tried with MDAC
> 2.8, MDAC 2.7 and restarted the Server, nothing is
> working. We are always getting the Same Error
> Error 7399: OLE DB Provider 'MSDAORA' Reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80004005: ].
> But i tried with one of the Windows 2000 machine, its
> working fine, Please help...
> Thanks in advance, NEED URgent HELP, Whole project gets
> stopped because of THIS.
> Thanks
> Paul
>
|||I just opened a crit sit with MS to get the answer to this. Here is the
fix with Windows 2003, SQL 2000, and Oracle 9i:
This is discussed in Q193893 and according to this the registry key "
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I] " should have
following
entries.
"OracleXaLib"="oraclient9.dll"
"OracleSqlLib"="orasql9.dll"
"OracleOciLib"="oci.dll"
I hope this helps.
Carrie
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

server: Can't create server but I can connect from one server to the other

I have 3 servers in this equation
server1 server2 and link1
I can create a linked server between server1->link1
I get an error when I try and do the exact same thing
between server2->link1
Error:
TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user reader
OLE DB provider "SQLNCLI" for linked server "link1" returned message
"Communication link failure". (Microsoft SQL Server, Error:10054)
So then I went through some troubleshooting on server2
Patch levels of windows and sql server seem to be exactly the same as
server1
I can remote to server2 and register link1 and it connects fine and
also allows
me to run queries.
Any ideas?
On Jun 21, 7:26 pm, Mandible <elliottj...@.gmail.com> wrote:
> I have 3 servers in this equation
> server1 server2 and link1
> I can create a linked server between server1->link1
> I get an error when I try and do the exact same thing
> between server2->link1
> Error:
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Login failed for user reader
> OLE DB provider "SQLNCLI" for linked server "link1" returned message
> "Communication link failure". (Microsoft SQL Server, Error:10054)
> So then I went through some troubleshooting on server2
> Patch levels of windows and sql server seem to be exactly the same as
> server1
> I can remote to server2 and register link1 and it connects fine and
> also allows
> me to run queries.
> Any ideas?
Are you registerting with TCP IP protocol. Check whether this enabled
on server2 .
Check for any firewall settings which may not be allowing to connect
throgh link server . I hope you enabled remote access allowed on both
the systems

server: Can't create server but I can connect from one server to the other

I have 3 servers in this equation
server1 server2 and link1
I can create a linked server between server1->link1
I get an error when I try and do the exact same thing
between server2->link1
Error:
TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user reader
OLE DB provider "SQLNCLI" for linked server "link1" returned message
"Communication link failure". (Microsoft SQL Server, Error:10054)
So then I went through some troubleshooting on server2
Patch levels of windows and sql server seem to be exactly the same as
server1
I can remote to server2 and register link1 and it connects fine and
also allows
me to run queries.
Any ideas?On Jun 21, 7:26 pm, Mandible <elliottj...@.gmail.com> wrote:
> I have 3 servers in this equation
> server1 server2 and link1
> I can create a linked server between server1->link1
> I get an error when I try and do the exact same thing
> between server2->link1
> Error:
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Login failed for user reader
> OLE DB provider "SQLNCLI" for linked server "link1" returned message
> "Communication link failure". (Microsoft SQL Server, Error:10054)
> So then I went through some troubleshooting on server2
> Patch levels of windows and sql server seem to be exactly the same as
> server1
> I can remote to server2 and register link1 and it connects fine and
> also allows
> me to run queries.
> Any ideas?
Are you registerting with TCP IP protocol. Check whether this enabled
on server2 .
Check for any firewall settings which may not be allowing to connect
throgh link server . I hope you enabled remote access allowed on both
the systems

server with Oracle

I installed the Oracle client on my computer, and I can connect to oracle databases using SqlPlus, however when I try setting up the linked server I get the following error after I try executing a query.
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12560: TNS:protocol adapter error
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

me to getting the same problem so u got the solution please tell to me

my Address:
A. Shiva Prasad
GIS Developer
MIDWEST INFO TECH Pvt Limited
70, kanakapura main road ,J.P. nagar 6th phase
opposite Family mart
Bangalore - INDIA
mail id : shiva.prasad@.midwestinfotech.com alternate mail : asp.347@.gmail.com
mobile : +919886451711

|||This looks more like a protocol adapter error. This indicates that Oracle client does not know what instance to connect to or what TNS alias to use. Try fixing the tnsname.ora file so that it points to the correct instance

server with Oracle

I installed the Oracle client on my computer, and I can connect to oracle databases using SqlPlus, however when I try setting up the linked server I get the following error after I try executing a query.
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12560: TNS:protocol adapter error
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].me to getting the same problem so u got the solution please tell to me

my Address:
A. Shiva Prasad
GIS Developer
MIDWEST INFO TECH Pvt Limited
70, kanakapura main road ,J.P. nagar 6th phase
opposite Family mart
Bangalore - INDIA
mail id : shiva.prasad@.midwestinfotech.com alternate mail : asp.347@.gmail.com
mobile : +919886451711

|||This looks more like a protocol adapter error. This indicates that Oracle client does not know what instance to connect to or what TNS alias to use. Try fixing the tnsname.ora file so that it points to the correct instance

server with IBM DB2 UDB for iSeries IDMDASQL OLE Provider Errors

Hello Everyone

I’m having problems retrieving data from IBM DB2 AS/400 linked server

I have managed to connect a linked server using the IBM DB2 UDB for iSeries "IBMDASQL" OLE DB Provider.

But when I run a query to the database I get only the field names and the following errors:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "iseries" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "IBMDASQL" for linked server "iseries".

My Server is Standard 64bit edition on cluster with CTP 2 installed .

I don’t have the right to use Microsoft OLE DB provider …..?

Thank you.

Firstly check to correct the Access Denied error and ensure the user you are using to COnnect to IBM DB2 should have access.

Also check this http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?forum=292&thread=143817&cat=5 link on IBM forums.

|||

Hello Satya

Thank you for your time.

I had my DB2 user tested with a non cluster 32bit installation.

I already posted my case to mentioned IBM link.

My problem is that I can’t use a Local system account for my 64bit clustered environment.

After executing a simple select query against the linked server I get an empty result set at the result tab with all table fields included and the errors

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "GRATHD1" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "IBMDASQL" for linked server "GRATHD1".

at the messages tab.

Thank you

|||

I would like to know how you setup the link. I have tried repeatedly to get a link for AS/400 to work, but can't seem to find the proper combination of parameters. Searching the internet has provided no help to me.

Thanks

|||

Hello Lee

I'm posting the script produced by my linked server

Notice that if you set the SQL Server service account to "local system" the linked server works.

/****** Object: LinkedServer [GRATHD1] Script Date: 01/30/2007 11:15:54 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'GRATHD1', @.srvproduct=N'IBM DB2 UDB for GRATHD1', @.provider=N'IBMDASQL', @.datasrc=N'GRATHD1'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'GRATHD1',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'as400username',@.rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'GRATHD1',@.useself=N'False',@.locallogin=N'DomainName\UserName',@.rmtuser=N'AS400username',@.rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'rpc', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'rpc out', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'use remote collation', @.optvalue=N'true'

|||

you musto add the options

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

|||

PS to the Microsoft people

When the server is running under the local system account and not a domain user this option is not necessary.

server to Visual FoxPro Not Work

I've downloaded and installed the latest VFPOLEDB (12/04) on 2 separate SQL Server boxes.

In both cases, If I connect to SQL Server with Query Analyzer as (local) while on a box, the linked server to my foxpro database works fine with openquery().

However, If I'm at one box and attached to SQL Server on the other box, the openquery() fails.

Here's some particulars:
--
EXEC sp_addlinkedserver
@.server='VFP',
@.provider='VFPOLEDB',
@.datasrc='\\hdmcpdctis1\tisrnddata\',
@.srvproduct='Visual FoxPro'

--this works on either (local) box
SELECT *
FROM OPENQUERY(VFP, 'select * from tislists')
Go

--but, the same openquery() above doesn't work if the box I'm running it from is attached to the SQL Server on the other box. I get:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'VFPOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for VFPOLEDB returned 0x80040154].
=====================
One other approach I tried that works while on the (local) box, but fails when attached to the SQL Server on the other box:

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB= \\hdmcpdctis1\tisrnddata\ ',
'select * from [tislists.DBF]')


With error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
===========================

Any Help is greatly appreciated! Thanks,

peter :confused:Hi,

Do you still have this problem? If not, how did you get around it? I am experiencing the exact same thing and am quite confused at this point...

Thanks!|||At my last job I handled a few linked Visual FoxPro linked serves from a SQL 7 database and I did'nt encounter this but let me ask you 2 questions.

1. Are you able query any database in BOX B from BOX A?

2. Have you tried querying using the 4 part name? linked_server_name.catalog.schema.object_name|||Answers to the questions:

1) Yes I can query some DB's from BOX A while on BOX B, the success depends on what authentication type the SQL Server instance is registered with in my enterprise manager

2) I have not tried querying with the 4 part name, I have only gotten as far as trying to view the list of tables through enterprise manager when I get the error

Thanks|||Can you see the tables is enterprise manager on the local box?

If not you have problem with your linked server definition. I am leaving for the day but I can check back on this this evening if I am not totally dead to the world.|||I can see the tables in EM on the local box, just not from the remote box.

Good luck on the interview...

server to Oracle doesnt fetch correct values

I am using the open query method to connect a Oracle server.
Below is my code to connect to oracle,when I execute the same query in oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
I have tried only one record based on id..sqlserver query returns 0 rows..whereas the oracle returns 4 rows..Can some one tell me what will be the problem

SET QUOTED_IDENTIFIER OFF
declare @.sql varchar(750)
select @.sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed', 'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not null ORDER BY A.CNUMBER
" + '")'
EXEC (@.SQL)

thanks
PriyaSo you're saying that PTTSTATUS is an Oracle server linked to from SQLServer, and when you execute the query "SELECT A.PROJECT_ID... ORDER BY A.CNUMBER" from SQLServer it returns less rows than when you execute the same query on Oracle?

What happens if you execute the query from SQL directly?

server to oracle doesn't fetch correct datavalues

I am using the open query method to connect a Oracle server.
Below is my code to connect to oracle,when I execute the same query in
oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
I have tried only one record based on id..sqlserver query returns 0
rows..whereas the oracle returns 4 rows..Can some one tell me what will be
the problem
SET QUOTED_IDENTIFIER OFF
declare @.sql varchar(750)
select @.sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT
A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed',
'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not null
ORDER BY A.CNUMBER
" + '")'
EXEC (@.SQL)
thanks
GP
Sorry I am connecting to two different databases.That's why the results are
different.
"GP" wrote:

> I am using the open query method to connect a Oracle server.
> Below is my code to connect to oracle,when I execute the same query in
> oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
> I have tried only one record based on id..sqlserver query returns 0
> rows..whereas the oracle returns 4 rows..Can some one tell me what will be
> the problem
> SET QUOTED_IDENTIFIER OFF
> declare @.sql varchar(750)
> select @.sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT
> A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
> FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed',
> 'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not null
> ORDER BY A.CNUMBER
> " + '")'
> EXEC (@.SQL)
> thanks
> GP

server to oracle doesn't fetch correct datavalues

I am using the open query method to connect a Oracle server.
Below is my code to connect to oracle,when I execute the same query in
oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
I have tried only one record based on id..sqlserver query returns 0
rows..whereas the oracle returns 4 rows..Can some one tell me what will be
the problem
SET QUOTED_IDENTIFIER OFF
declare @.sql varchar(750)
select @.sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT
A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed',
'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not nul
l
ORDER BY A.CNUMBER
" + '")'
EXEC (@.SQL)
thanks
GPSorry I am connecting to two different databases.That's why the results are
different.
"GP" wrote:

> I am using the open query method to connect a Oracle server.
> Below is my code to connect to oracle,when I execute the same query in
> oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
> I have tried only one record based on id..sqlserver query returns 0
> rows..whereas the oracle returns 4 rows..Can some one tell me what will be
> the problem
> SET QUOTED_IDENTIFIER OFF
> declare @.sql varchar(750)
> select @.sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT
> A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
> FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed'
,
> 'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not n
ull
> ORDER BY A.CNUMBER
> " + '")'
> EXEC (@.SQL)
> thanks
> GP

server to Oracle 9i

I'm connecting to Oracle 8i databases from SQL Server through Linked Server. Now I need to connect to Oracle9i databases from the same SQL Server. What currently I'm doing is: (already installed Oracle 9i client tools on the Server machine) changing the r
egistry setting of "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxO CI]"
to the respective Oracle 9i DLLs, overriding Oracle 8i DLLS.
Again, if I want to connect to Oracle 8i databases, again I'm changing the registry to point to the 8i DLLs.
My question is: is there any possibility of connecting to both Oracle 8i and 9i databases from one SQL Server machine?
Thanks..
Siva
"Siva" <siva116@.yahoo.com> wrote in message
news:19BE8276-E801-490F-B9B5-EC46DDC600CA@.microsoft.com...
> I'm connecting to Oracle 8i databases from SQL Server through Linked
Server. Now I need to connect to Oracle9i databases from the same SQL
Server. What currently I'm doing is: (already installed Oracle 9i client
tools on the Server machine) changing the registry setting of
"[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxO CI]"
> to the respective Oracle 9i DLLs, overriding Oracle 8i DLLS.
> Again, if I want to connect to Oracle 8i databases, again I'm changing the
registry to point to the 8i DLLs.
> My question is: is there any possibility of connecting to both Oracle 8i
and 9i databases from one SQL Server machine?
The Oracle client version is not tied to the Oracle server version. Either
client version should be able to connect to either server version, so just
user the Oracle 9i client.
David

server to Oracle 9i

I'm connecting to Oracle 8i databases from SQL Server through Linked Server. Now I need to connect to Oracle9i databases from the same SQL Server. What currently I'm doing is: (already installed Oracle 9i client tools on the Server machine) changing the registry setting of "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
to the respective Oracle 9i DLLs, overriding Oracle 8i DLLS.
Again, if I want to connect to Oracle 8i databases, again I'm changing the registry to point to the 8i DLLs
My question is: is there any possibility of connecting to both Oracle 8i and 9i databases from one SQL Server machine
Thanks.
Siva"Siva" <siva116@.yahoo.com> wrote in message
news:19BE8276-E801-490F-B9B5-EC46DDC600CA@.microsoft.com...
> I'm connecting to Oracle 8i databases from SQL Server through Linked
Server. Now I need to connect to Oracle9i databases from the same SQL
Server. What currently I'm doing is: (already installed Oracle 9i client
tools on the Server machine) changing the registry setting of
"[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]"
> to the respective Oracle 9i DLLs, overriding Oracle 8i DLLS.
> Again, if I want to connect to Oracle 8i databases, again I'm changing the
registry to point to the 8i DLLs.
> My question is: is there any possibility of connecting to both Oracle 8i
and 9i databases from one SQL Server machine?
The Oracle client version is not tied to the Oracle server version. Either
client version should be able to connect to either server version, so just
user the Oracle 9i client.
David

server to Oracle 9i

I'm connecting to Oracle 8i databases from SQL Server through Linked Server.
Now I need to connect to Oracle9i databases from the same SQL Server. What
currently I'm doing is: (already installed Oracle 9i client tools on the Ser
ver machine) changing the r
egistry setting of "& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSDTC\MTxOCI]
"
to the respective Oracle 9i DLLs, overriding Oracle 8i DLLS.
Again, if I want to connect to Oracle 8i databases, again I'm changing the r
egistry to point to the 8i DLLs.
My question is: is there any possibility of connecting to both Oracle 8i and
9i databases from one SQL Server machine?
Thanks..
Siva"Siva" <siva116@.yahoo.com> wrote in message
news:19BE8276-E801-490F-B9B5-EC46DDC600CA@.microsoft.com...
> I'm connecting to Oracle 8i databases from SQL Server through Linked
Server. Now I need to connect to Oracle9i databases from the same SQL
Server. What currently I'm doing is: (already installed Oracle 9i client
tools on the Server machine) changing the registry setting of
"& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSDTC\MTxOCI]"
> to the respective Oracle 9i DLLs, overriding Oracle 8i DLLS.
> Again, if I want to connect to Oracle 8i databases, again I'm changing the
registry to point to the 8i DLLs.
> My question is: is there any possibility of connecting to both Oracle 8i
and 9i databases from one SQL Server machine?
The Oracle client version is not tied to the Oracle server version. Either
client version should be able to connect to either server version, so just
user the Oracle 9i client.
David

server to Oracle

Hello Group:

I use a Linked Server to connect to a Oracle 8i base. Now I want to implement a distributed transaction using this Linked Server: Two-Phase Commit, but it doesn't work. I can send a "begin tran", followed by an "update" at the SQL 2000, but when I send a query to Linked Server, a simple "select", I get this error:
Server: Msg 7395, Level 16, State 2, Line 1
Unable to start a nested transaction for OLE DB provider 'MSDAORA'. A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

I try the Oracle OLE DB Provider without sucess.

Can anyone help me ?

Thank you,

Aldair.Try sending simple queries to the oracle server & see what you get. Then gradually work up to nested transactions - keep it simple through each step of troubleshooting.

Also, do some research on the version of driver you are using - some are known to have bugs similar to what you describe.

Post back if problems,,

Cheers,

SG|||Well, because the Msg 7395 says "...XACT_ABORT..." is set to OFF, and a consult the BOL, I try set XACT_ABORT to ON. After this, I can do a distributed transaction. I send "begin tran" at SQL Srv, then send "update" at SQL Srv (OK), then I send an Update do Oracle, using Linked Server, and it works fine.
But, another problem, the linked server connection to Oracle hold locks for a long time, then another transaction that send a "insert", for instance, receive a time-out failure.

Can I force the linked server to Oracle to free locks ? How?

TIA,

Aldair.|||Well, because the Msg 7395 says "...XACT_ABORT..." is set to OFF, and a consult the BOL, I try set XACT_ABORT to ON. After this, I can do a distributed transaction. I send "begin tran" at SQL Srv, then send "update" at SQL Srv (OK), then I send an Update do Oracle, using Linked Server, and it works fine.
But, another problem, the linked server connection to Oracle hold locks for a long time, then another transaction that send a "insert", for instance, receive a time-out failure.

Can I force the linked server to Oracle to free locks ? How?

TIA,

Aldair.|||Hi

When you send a command to oracle, check the type of default locking that oracle uses - in SQL its read uncommitted for a transaction but oracle may be different. You can specify locking hints in your transactions BUT oracle may NOT recognise them, so you may need to customise the distributed transactions using oracle commands accordingly.

This type of problem requires slow, careful & methodical testing - dont rush it.

Post back if problems

Cheers

SG|||Hi.

Thank you for yours helps. I will try with care.

SYL.

Aldair.