linking two datasets: February 2012

Friday, February 24, 2012

server to Pervasive 8 - Connection Error

Hello,

I am currently migrating from SQL 7.0 to SQL2005. So far everything is going well except for one thing, my linked server to a Pervasive 8.6 server database. I've done the same configurations as with the SQL 7.0 server to create the linked server. I've created an Pervasive ODBC Client DSN system connection on the server called PervasiveDB pointing to the DataDB catalog, using TCP/IP as transport and the server name "PervasiveDB" as the server address. From this point, on the SQL2005 server, the DSN connection is successfull and I can see the different catalog names of the PervasiveDB server. When I create the Linked server on the SQL2005, I create exactly as done on the SQL7.0 which is :

SQL7.0 server : OS is Windows 2000 all service packs applied for both OS and SQL7.0

SQL2005 server : OS is Windows2003 standard edition all service packs applied for both OS and SQL2005.

Linked server name : TestPervasive

Provider : Microsoft OLE DB Provider for ODBC Drivers

Product name: Pervasive

Data source: PervasiveDB

Location: PervasiveDB

Catalog : DataDB

I get the following error when I try to query the data to test if the linked server I just created is successful:

query : SELECT * FROM testpervasive.DataDB..schedule

error message:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "TestPervasive" 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 "MSDASQL" for linked server "TestPervasive".

Anybody can help me on this one? I would greatly appreciate it !!

Hello,

I am relatively new at posting at these forums, but I wanted to advise that the problem I experience above has been resolved. I re-installed the service pack for SQL2005 and re-booted and now works fine with the above configurations/settings.

server to oracle9i

What is the procedure to set up a linked server to Oracle9i from sql 2000.
ThanksSee sp_addlinkedserver
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
C. Use the Microsoft OLE DB Provider for Oracle
This example creates a linked server named LONDON Mktg that uses the
Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for
the Oracle database is MyServer.
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'LONDON Mktg',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'MyServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'LONDON Mktg',
'Oracle',
'MSDAORA',
'MyServer'
GO
And read http://support.microsoft.com/?id=280106 How to set up and
troubleshoot a linked server to Oracle in SQL ServerGertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:CEDEF37A-F2F4-4D0C-8318-286CF728FD20@.microsoft.com...
> What is the procedure to set up a linked server to Oracle9i from sql 2000.
> Thanks

server to oracle9i

What is the procedure to set up a linked server to Oracle9i from sql 2000.
Thanks
See sp_addlinkedserver
http://msdn.microsoft.com/library/de..._adda_8gqa.asp
C. Use the Microsoft OLE DB Provider for Oracle
This example creates a linked server named LONDON Mktg that uses the
Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for
the Oracle database is MyServer.
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'LONDON Mktg',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'MyServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'LONDON Mktg',
'Oracle',
'MSDAORA',
'MyServer'
GO
And read http://support.microsoft.com/?id=280106 How to set up and
troubleshoot a linked server to Oracle in SQL ServerGertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:CEDEF37A-F2F4-4D0C-8318-286CF728FD20@.microsoft.com...
> What is the procedure to set up a linked server to Oracle9i from sql 2000.
> Thanks

server to oracle9i

What is the procedure to set up a linked server to Oracle9i from sql 2000.
ThanksSee sp_addlinkedserver
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
C. Use the Microsoft OLE DB Provider for Oracle
This example creates a linked server named LONDON Mktg that uses the
Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for
the Oracle database is MyServer.
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'LONDON Mktg',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'MyServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'LONDON Mktg',
'Oracle',
'MSDAORA',
'MyServer'
GO
And read http://support.microsoft.com/?id=280106 How to set up and
troubleshoot a linked server to Oracle in SQL ServerGertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:CEDEF37A-F2F4-4D0C-8318-286CF728FD20@.microsoft.com...
> What is the procedure to set up a linked server to Oracle9i from sql 2000.
> Thanks

server to Oracle via ODBC, return less records

I have an Oracle 9i server. To access the data in Oracle, I setup an ODBC connection to it and am able to return data from it using WinSQL (a general ODBC database client). The SQL statement is simply "SELECT * FROM COLOR" and all 133 records returned properly.

I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above.

The problem is: only 32 records returned.

May I ask if there is any problem to this linked server setup?

Thanks.

Does any one has any idea..please help.^^

|||

I forwarded this to a linked-server guru here at Microsoft and here's the reply he gave:

Usually provider bugs. Updating the client versions for Oracle's code often fixes this class of issue.

Note that Kagera isn't really fully supported anymore either.

Oracle's native OLE/DB provider is the usual choice instead of ODBC.

Hope this is helpful.

|||

Same problem here,

created a query within Oracle SQLplus that returns 344.743 records. When I use the same query within the SSIS package it only returns 60.000 records? I'm connected with OLE DB and installed the latest Oracle 9i client. When I add a count within the query in SSIS is also concludes that there are 344.743 records.

Urgent help needed because we are migrating from DTS to SSIS.

Any idea what the problem can be?

Thankz

Ron Killaars

IT Engineer Energie Big Smile irect BV

server to Oracle via ODBC, return less records

I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above.

The problem is: only 32 records returned.

May I ask if there is any problem to this linked server setup?

Thanks.

Does any one has any idea..please help.^^

|||

I forwarded this to a linked-server guru here at Microsoft and here's the reply he gave:

Usually provider bugs. Updating the client versions for Oracle's code often fixes this class of issue.

Note that Kagera isn't really fully supported anymore either.

Oracle's native OLE/DB provider is the usual choice instead of ODBC.

Hope this is helpful.

|||

Same problem here,

Urgent help needed because we are migrating from DTS to SSIS.

Any idea what the problem can be?

Thankz

Ron Killaars

IT Engineer Energie Big Smile irect BV

server to Oracle using SQL Server 2005

I am trying to set a linked server for an Oracle base(9.2) to SQL Server
2005(June CTP).
I am able to set up the linked server but when I execute a query then I get
the following error
The OLE DB provider "MSDAORA.1" for linked server "ORACLDB" reported an
error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
provider "MSDAORA.1" for linked server "ORACLDB".
I am able to connect to this ORACLE base using SQL PLUS. I am also able to
extract data using SSIS.
Thanks
ShriInstall the Oracle Client tools should help.
You need to have Oracle Client installed
--
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Shri Rao" <Shri Rao@.discussions.microsoft.com> wrote in message
news:1E6D4028-A7B3-4995-B71B-83FBB8FFB4B5@.microsoft.com...
>I am trying to set a linked server for an Oracle base(9.2) to SQL Server
> 2005(June CTP).
> I am able to set up the linked server but when I execute a query then I
> get
> the following error
> The OLE DB provider "MSDAORA.1" for linked server "ORACLDB" reported an
> error. Access denied.
> Msg 7301, Level 16, State 2, Line 1
> Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
> provider "MSDAORA.1" for linked server "ORACLDB".
>
> I am able to connect to this ORACLE base using SQL PLUS. I am also able to
> extract data using SSIS.
> Thanks
> Shri|||I do have the Oracle client installed and I am able to connect using SQL+, I
am also able to extract the data using SSIS.
"Dandy Weyn [Dandyman]" wrote:
> Install the Oracle Client tools should help.
> You need to have Oracle Client installed
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> "Shri Rao" <Shri Rao@.discussions.microsoft.com> wrote in message
> news:1E6D4028-A7B3-4995-B71B-83FBB8FFB4B5@.microsoft.com...
> >I am trying to set a linked server for an Oracle base(9.2) to SQL Server
> > 2005(June CTP).
> > I am able to set up the linked server but when I execute a query then I
> > get
> > the following error
> >
> > The OLE DB provider "MSDAORA.1" for linked server "ORACLDB" reported an
> > error. Access denied.
> > Msg 7301, Level 16, State 2, Line 1
> > Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
> > provider "MSDAORA.1" for linked server "ORACLDB".
> >
> >
> > I am able to connect to this ORACLE base using SQL PLUS. I am also able to
> > extract data using SSIS.
> >
> > Thanks
> > Shri
>
>|||Don't use the Microsoft OLEDB Provider for Oracle, use the Oracle one
--
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Shri Rao" <ShriRao@.discussions.microsoft.com> wrote in message
news:8BC6A8EA-C372-442D-B793-B7C84DDCF751@.microsoft.com...
>I do have the Oracle client installed and I am able to connect using SQL+,
>I
> am also able to extract the data using SSIS.
> "Dandy Weyn [Dandyman]" wrote:
>> Install the Oracle Client tools should help.
>> You need to have Oracle Client installed
>> --
>> Dandy Weyn
>> [MCSE-MCSA-MCDBA-MCDST-MCT]
>> http://www.dandyman.net
>> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
>> "Shri Rao" <Shri Rao@.discussions.microsoft.com> wrote in message
>> news:1E6D4028-A7B3-4995-B71B-83FBB8FFB4B5@.microsoft.com...
>> >I am trying to set a linked server for an Oracle base(9.2) to SQL Server
>> > 2005(June CTP).
>> > I am able to set up the linked server but when I execute a query then I
>> > get
>> > the following error
>> >
>> > The OLE DB provider "MSDAORA.1" for linked server "ORACLDB" reported an
>> > error. Access denied.
>> > Msg 7301, Level 16, State 2, Line 1
>> > Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE
>> > DB
>> > provider "MSDAORA.1" for linked server "ORACLDB".
>> >
>> >
>> > I am able to connect to this ORACLE base using SQL PLUS. I am also able
>> > to
>> > extract data using SSIS.
>> >
>> > Thanks
>> > Shri
>>

server to Oracle on Unix

Hi all!
I have a SQL Server from which I need to query a view in a Oracle database.
I have created a linked server connection to the Oracle machine. Oracle,
version 9i, is running on Unix.
Now when I try to use the linked server connection I have some problems.
When I expand the linked server and try to see the tables and views I
sometimes get an error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Sometimes everything works just fine and sometimes I get the error message
above. When the developers are trying to query the Oracle view they
sometimes get the same error and sometimes the queries works just fine.
The thing is that I don't know anything about Unix so I don't know if there
is something I can do on that side.
Any help or suggestions would be greatly appreciated.A few things you can try:
When the developers are querying Oracle, they can turn 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 the queries against Oracle.
Also, pay attention to the time it takes to execute the
queries when they fail and when they are successful. You
could be experiencing remote query timeouts. You can try
increasing the setting using sp_configure. You can find more
information in books online on using sp_configure.
You will also 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 Tue, 16 Nov 2004 12:18:31 +0100, "Jaana"
<jaana.lehtonen@.banverket.se> wrote:

>Hi all!
>I have a SQL Server from which I need to query a view in a Oracle database.
>I have created a linked server connection to the Oracle machine. Oracle,
>version 9i, is running on Unix.
>Now when I try to use the linked server connection I have some problems.
>When I expand the linked server and try to see the tables and views I
>sometimes get an error message.
>Error 7399: OLE DB provider 'MSDAORA' reported an error.
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>Sometimes everything works just fine and sometimes I get the error message
>above. When the developers are trying to query the Oracle view they
>sometimes get the same error and sometimes the queries works just fine.
>The thing is that I don't know anything about Unix so I don't know if there
>is something I can do on that side.
>Any help or suggestions would be greatly appreciated.
>
>

server to Oracle on Unix

Hi all!
I have a SQL Server from which I need to query a view in a Oracle database.
I have created a linked server connection to the Oracle machine. Oracle,
version 9i, is running on Unix.
Now when I try to use the linked server connection I have some problems.
When I expand the linked server and try to see the tables and views I
sometimes get an error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Sometimes everything works just fine and sometimes I get the error message
above. When the developers are trying to query the Oracle view they
sometimes get the same error and sometimes the queries works just fine.
The thing is that I don't know anything about Unix so I don't know if there
is something I can do on that side.
Any help or suggestions would be greatly appreciated.
A few things you can try:
When the developers are querying Oracle, they can turn 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 the queries against Oracle.
Also, pay attention to the time it takes to execute the
queries when they fail and when they are successful. You
could be experiencing remote query timeouts. You can try
increasing the setting using sp_configure. You can find more
information in books online on using sp_configure.
You will also 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 Tue, 16 Nov 2004 12:18:31 +0100, "Jaana"
<jaana.lehtonen@.banverket.se> wrote:

>Hi all!
>I have a SQL Server from which I need to query a view in a Oracle database.
>I have created a linked server connection to the Oracle machine. Oracle,
>version 9i, is running on Unix.
>Now when I try to use the linked server connection I have some problems.
>When I expand the linked server and try to see the tables and views I
>sometimes get an error message.
>Error 7399: OLE DB provider 'MSDAORA' reported an error.
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>Sometimes everything works just fine and sometimes I get the error message
>above. When the developers are trying to query the Oracle view they
>sometimes get the same error and sometimes the queries works just fine.
>The thing is that I don't know anything about Unix so I don't know if there
>is something I can do on that side.
>Any help or suggestions would be greatly appreciated.
>
>

server to Oracle -OLE/DB Provider 'MSDAORA' IDBInitialize

Hi All ,
We have a WIN NT 4.0 Server with SQL 7.0 (7.00.961) Connecting to Oracle 8 DB using linked server . With Trace flag 7300 on we get an error message as follows.We are using Oracle 9 i Client
Thank you , Shaji
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: An Oracle Error occured, but message could not be determined]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
We use MS OLE DB Provider for Oracle with following parameters
Product name : Oracle
Data Source : DEVP
Provider string :MSDAORA
With relevant Remote user name and Password . The issue still remain the same.

Thank you for the fast response
Shaji
|||With it being an initialization error, you may want to make
sure the provider is registered - try registering
msdora.dll. If you get any errors, try reinstalling mdac.
You may also want to refer to the following:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Tue, 20 Apr 2004 00:41:02 -0700, "Shaji"
<anonymous@.discussions.microsoft.com> wrote:

>We use MS OLE DB Provider for Oracle with following parameters
>Product name : Oracle
>Data Source : DEVP
>Provider string :MSDAORA
>With relevant Remote user name and Password . The issue still remain the same.

>Thank you for the fast response
>Shaji
>

server to Oracle doesnt fetch correct values

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 Causes SQL Server Instance to Crash

When running a linked server to Oracle using the MSDAORA driver using in-process option checked, I'm getting the following error:

"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)"

This then causes the instance to crash.

In the Event Log, it shows:

SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).

When using the out-of-process, it shows the column names in the query results window, but doesn't show any data then immediately in the message window it shows:

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "MSDAORA" for linked server "smtest" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 2

Cannot get the column information from OLE DB provider "MSDAORA" for linked server "smtest".

We're using:

SQL Server 2005 SP1 with hot fixes (9.0.2153) 32-bit on 64-bit o/s Windows 2003 R2 Standard x64 Edition Sevice Pack 1

We use lots of linked servers to Oracle on other servers, and can't find anything different on this one.

Appreciate any thoughts, insights!

thanks,

Steve
I have the same problem. Microsoft suggested to edit the sqlnet.ora file so onames comes before tnsnames. So far, this alone has not solved my problem, i'm still digging.|||

Emily,

We found that the problem was caused by a permissions issue on the oci.dll (oracle client .dll).

The way we found it was by using FILEMON (sysinternals site) to find access denied to the Oracle files.

Hope this helps.

Steve

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 9.2 database

I've read KB Article 280106 about creating a Linked Server to an Oracle
database. This article does reference any Oracle database higher than 8.1.*
.
I want to link to a 9.2.0.1 database.
The article references loading the Oracle client software for 8.1 on the
Sqlserver machine.
Is this possible to create the link server if I load the client software for
9.2 on the Sqlserver machine? If it is, is it as easy as changing to
registry settings referenced in the Article for the 8.1 database from using
the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll tha
t
come with the 9.2 client software?
The other option is if I load the 8.1 client software on the Sqlserver
machine to connect to the 9.2 database, would this work?"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> I've read KB Article 280106 about creating a Linked Server to an Oracle
> database. This article does reference any Oracle database higher than
> 8.1.*.
> I want to link to a 9.2.0.1 database.
> The article references loading the Oracle client software for 8.1 on the
> Sqlserver machine.
> Is this possible to create the link server if I load the client software
> for
> 9.2 on the Sqlserver machine?
Yes.

>If it is, is it as easy as changing to
> registry settings referenced in the Article for the 8.1 database from
> using
> the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll
> that
> come with the 9.2 client software?
Not necessary to change the registry.. The 9i client, or the 10g client
will work for linked server.

> The other option is if I load the 8.1 client software on the Sqlserver
> machine to connect to the 9.2 database, would this work?
>
It should work, yes. But you should probably use the 9i client.
David|||Thanks for your response
You are telling me that with the 9.2 client software that there is no
registry changes needed.
When I look at the registry setting for
& #123;HKEY_LOCAL_MACHINE\SOFTWARE\Microso
ft\MSDTC\MTxOCI}, the file for
OracleXaLib value that is set by default is a file that don't exist in the
ORACLE_HOME/bin directory. Is this file not needed?
The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
database, should it be oraclient9.dll for the 9.2 database?
"David Browne" wrote:

> "Paul R" <Paul R@.discussions.microsoft.com> wrote in message
> news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> Yes.
>
> Not necessary to change the registry.. The 9i client, or the 10g client
> will work for linked server.
>
> It should work, yes. But you should probably use the 9i client.
> David
>
>|||"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:9BE76BF3-045E-4913-AFDD-6357E74A12B8@.microsoft.com...
> Thanks for your response
> You are telling me that with the 9.2 client software that there is no
> registry changes needed.
> When I look at the registry setting for
> & #123;HKEY_LOCAL_MACHINE\SOFTWARE\Microso
ft\MSDTC\MTxOCI}, the file for
> OracleXaLib value that is set by default is a file that don't exist in the
> ORACLE_HOME/bin directory. Is this file not needed?
> The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
> database, should it be oraclient9.dll for the 9.2 database?
>
Those settings should probably match your Oracle Client install version (not
the database version). Note that for the 8i client and 9 client, the OCI
library has the same name (oci.dll). This should be enough for basic
connectivity. For distributed transactions (XA and DTC) you may need to
configure the other registry keys. For basic linked server functionality
you do not need to change the registry.
David

server to Oracle 9.2 database

I've read KB Article 280106 about creating a Linked Server to an Oracle
database. This article does reference any Oracle database higher than 8.1.*.
I want to link to a 9.2.0.1 database.
The article references loading the Oracle client software for 8.1 on the
Sqlserver machine.
Is this possible to create the link server if I load the client software for
9.2 on the Sqlserver machine? If it is, is it as easy as changing to
registry settings referenced in the Article for the 8.1 database from using
the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll that
come with the 9.2 client software?
The other option is if I load the 8.1 client software on the Sqlserver
machine to connect to the 9.2 database, would this work?
"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> I've read KB Article 280106 about creating a Linked Server to an Oracle
> database. This article does reference any Oracle database higher than
> 8.1.*.
> I want to link to a 9.2.0.1 database.
> The article references loading the Oracle client software for 8.1 on the
> Sqlserver machine.
> Is this possible to create the link server if I load the client software
> for
> 9.2 on the Sqlserver machine?
Yes.

>If it is, is it as easy as changing to
> registry settings referenced in the Article for the 8.1 database from
> using
> the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll
> that
> come with the 9.2 client software?
Not necessary to change the registry.. The 9i client, or the 10g client
will work for linked server.

> The other option is if I load the 8.1 client software on the Sqlserver
> machine to connect to the 9.2 database, would this work?
>
It should work, yes. But you should probably use the 9i client.
David
|||Thanks for your response
You are telling me that with the 9.2 client software that there is no
registry changes needed.
When I look at the registry setting for
{HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I}, the file for
OracleXaLib value that is set by default is a file that don't exist in the
ORACLE_HOME/bin directory. Is this file not needed?
The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
database, should it be oraclient9.dll for the 9.2 database?
"David Browne" wrote:

> "Paul R" <Paul R@.discussions.microsoft.com> wrote in message
> news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> Yes.
>
> Not necessary to change the registry.. The 9i client, or the 10g client
> will work for linked server.
>
> It should work, yes. But you should probably use the 9i client.
> David
>
>
|||"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:9BE76BF3-045E-4913-AFDD-6357E74A12B8@.microsoft.com...
> Thanks for your response
> You are telling me that with the 9.2 client software that there is no
> registry changes needed.
> When I look at the registry setting for
> {HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I}, the file for
> OracleXaLib value that is set by default is a file that don't exist in the
> ORACLE_HOME/bin directory. Is this file not needed?
> The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
> database, should it be oraclient9.dll for the 9.2 database?
>
Those settings should probably match your Oracle Client install version (not
the database version). Note that for the 8i client and 9 client, the OCI
library has the same name (oci.dll). This should be enough for basic
connectivity. For distributed transactions (XA and DTC) you may need to
configure the other registry keys. For basic linked server functionality
you do not need to change the registry.
David

server to Oracle 9.2 database

I've read KB Article 280106 about creating a Linked Server to an Oracle
database. This article does reference any Oracle database higher than 8.1.*.
I want to link to a 9.2.0.1 database.
The article references loading the Oracle client software for 8.1 on the
Sqlserver machine.
Is this possible to create the link server if I load the client software for
9.2 on the Sqlserver machine? If it is, is it as easy as changing to
registry settings referenced in the Article for the 8.1 database from using
the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll that
come with the 9.2 client software?
The other option is if I load the 8.1 client software on the Sqlserver
machine to connect to the 9.2 database, would this work?"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> I've read KB Article 280106 about creating a Linked Server to an Oracle
> database. This article does reference any Oracle database higher than
> 8.1.*.
> I want to link to a 9.2.0.1 database.
> The article references loading the Oracle client software for 8.1 on the
> Sqlserver machine.
> Is this possible to create the link server if I load the client software
> for
> 9.2 on the Sqlserver machine?
Yes.
>If it is, is it as easy as changing to
> registry settings referenced in the Article for the 8.1 database from
> using
> the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll
> that
> come with the 9.2 client software?
Not necessary to change the registry.. The 9i client, or the 10g client
will work for linked server.
> The other option is if I load the 8.1 client software on the Sqlserver
> machine to connect to the 9.2 database, would this work?
>
It should work, yes. But you should probably use the 9i client.
David|||Thanks for your response
You are telling me that with the 9.2 client software that there is no
registry changes needed.
When I look at the registry setting for
{HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI}, the file for
OracleXaLib value that is set by default is a file that don't exist in the
ORACLE_HOME/bin directory. Is this file not needed?
The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
database, should it be oraclient9.dll for the 9.2 database?
"David Browne" wrote:
> "Paul R" <Paul R@.discussions.microsoft.com> wrote in message
> news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> > I've read KB Article 280106 about creating a Linked Server to an Oracle
> > database. This article does reference any Oracle database higher than
> > 8.1.*.
> > I want to link to a 9.2.0.1 database.
> >
> > The article references loading the Oracle client software for 8.1 on the
> > Sqlserver machine.
> >
> > Is this possible to create the link server if I load the client software
> > for
> > 9.2 on the Sqlserver machine?
> Yes.
> >If it is, is it as easy as changing to
> > registry settings referenced in the Article for the 8.1 database from
> > using
> > the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll
> > that
> > come with the 9.2 client software?
> Not necessary to change the registry.. The 9i client, or the 10g client
> will work for linked server.
> >
> > The other option is if I load the 8.1 client software on the Sqlserver
> > machine to connect to the 9.2 database, would this work?
> >
> It should work, yes. But you should probably use the 9i client.
> David
>
>|||"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:9BE76BF3-045E-4913-AFDD-6357E74A12B8@.microsoft.com...
> Thanks for your response
> You are telling me that with the 9.2 client software that there is no
> registry changes needed.
> When I look at the registry setting for
> {HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI}, the file for
> OracleXaLib value that is set by default is a file that don't exist in the
> ORACLE_HOME/bin directory. Is this file not needed?
> The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
> database, should it be oraclient9.dll for the 9.2 database?
>
Those settings should probably match your Oracle Client install version (not
the database version). Note that for the 8i client and 9 client, the OCI
library has the same name (oci.dll). This should be enough for basic
connectivity. For distributed transactions (XA and DTC) you may need to
configure the other registry keys. For basic linked server functionality
you do not need to change the registry.
David

server to Oracle 9.2

Greetings,
My customer is in the process of changing from Oracle
8.0.5 to 9.2.0. In one of there tables there is a field
that is defined as a number with no precision, and no
scale. When I look at the data in the field I find digits
on both sides on the decimal point.
The databases using Oracle 8.0.5, I have no problem
retriving the data. However those using Oracle 9.2.0, SQL
Server returns an error.
The error reads:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent
metadata for a column. Metadata information was changed at
execution time.
OLE DB error trace [Non-interface error:
Column 'ITEM_DISC_AMT' (compile-time ordinal 15) of
object '"MFG"."TMFG_ORDER_LINE_ITEM"' was reported to have
a DBTYPE of
130 at compile time and 5 at run time].
If I import the Oracle 8.0.5 version of the table into SQL
Server, I find the field defined as [numeric](38, 0]. When
I do the same with the Oracle 9.2.0 version, its defined
as [float].
Something I find interesting is MS Access does not have
any problem with either database.
My customer doesn't seem want to change the field
definition.
Can anyone recommend a way around this.
Thanks in advance!
MarkMark,
There are some limitations with clients Oracle 9i and above especially to
do with certain data types. Please see
244661 INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
http://support.microsoft.com/?id=244661
Note that the article comments: "If you require Oracle 9i functionality and
decide to use the Oracle 9i client, note that Microsoft has not tested this
configuration and that you may encounter problems."
I suggest testing out with the Microsoft .NET Managed Oracle Provider as
specified in the article.
Regards
James

server to Oracle 9.2

Greetings,
My customer is in the process of changing from Oracle
8.0.5 to 9.2.0. In one of there tables there is a field
that is defined as a number with no precision, and no
scale. When I look at the data in the field I find digits
on both sides on the decimal point.
The databases using Oracle 8.0.5, I have no problem
retriving the data. However those using Oracle 9.2.0, SQL
Server returns an error.
The error reads:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent
metadata for a column. Metadata information was changed at
execution time.
OLE DB error trace [Non-interface error:
Column 'ITEM_DISC_AMT' (compile-time ordinal 15) of
object '"MFG"."TMFG_ORDER_LINE_ITEM"' was reported to have
a DBTYPE of
130 at compile time and 5 at run time].
If I import the Oracle 8.0.5 version of the table into SQL
Server, I find the field defined as [numeric](38, 0]. When
I do the same with the Oracle 9.2.0 version, its defined
as [float].
Something I find interesting is MS Access does not have
any problem with either database.
My customer doesn't seem want to change the field
definition.
Can anyone recommend a way around this.
Thanks in advance!
Mark
Mark,
There are some limitations with clients Oracle 9i and above especially to
do with certain data types. Please see
244661 INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
http://support.microsoft.com/?id=244661
Note that the article comments: "If you require Oracle 9i functionality and
decide to use the Oracle 9i client, note that Microsoft has not tested this
configuration and that you may encounter problems."
I suggest testing out with the Microsoft .NET Managed Oracle Provider as
specified in the article.
Regards
James

server to Oracle - error on NVARCHAR2()

I have a linked server in a SQL 2000 DB - links to Oracle9 DB. When I query
tables with columns of type NVARCHAR2 I get an error. Does any one know of a
solution to this?
LMcPheeWhat's the error.
"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:96F46853-6BA2-40FC-BED6-2B391812BFF6@.microsoft.com...
> I have a linked server in a SQL 2000 DB - links to Oracle9 DB. When I
query
> tables with columns of type NVARCHAR2 I get an error. Does any one know of
a
> solution to this?
> LMcPhee

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.

server to Oracle

I'm trying to pass a query from sql server 2000 to Oracle using linked
servers. I don't want to use DTS. While it's easy enough to use OPENQUERY
to pass thru a query that returns a dataset, I can't seem to pass thru a
query that doesn't return a dataset eg a create table query or a drop table
query. If I try, I get an error saying that the query returns no columns.
Is it possible to pass thru a query that returns no columns using a linked
server?
For example, the following query:
select *
from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
returns the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
OLE DB provider 'MSDAORA' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='MSDAORA', Query=CREATE
TABLE MYTABLE AS SELECT * FROM EMP'].
arch (Sorry , cannot test it right now)
SELECT *
FROM
OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
If it does not work you may want to try
CREATE FUNCTION dbo.fn_getdata()
AS
RETURNS TABLE
AS
BEGIN
RETURN(
SELECT *
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
END
"arch" <archangel@.arach.net.au> wrote in message
news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au.. .
> I'm trying to pass a query from sql server 2000 to Oracle using linked
> servers. I don't want to use DTS. While it's easy enough to use
> OPENQUERY
> to pass thru a query that returns a dataset, I can't seem to pass thru a
> query that doesn't return a dataset eg a create table query or a drop
> table
> query. If I try, I get an error saying that the query returns no columns.
> Is it possible to pass thru a query that returns no columns using a linked
> server?
> For example, the following query:
> select *
> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
> returns the error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
> OLE DB provider 'MSDAORA' indicates that the object has no columns.
> OLE DB error trace [Non-interface error: OLE DB provider unable to
> process
> object, since the object has no columnsProviderName='MSDAORA',
> Query=CREATE
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>
|||No luck with any of that.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23A$6awKGHA.2628@.TK2MSFTNGP15.phx.gbl...
> arch (Sorry , cannot test it right now)
> SELECT *
> FROM
> OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
> If it does not work you may want to try
> CREATE FUNCTION dbo.fn_getdata()
> AS
> RETURNS TABLE
> AS
> BEGIN
> RETURN(
> SELECT *
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
> END
>
>
> "arch" <archangel@.arach.net.au> wrote in message
> news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au.. .
>

server to oracle

Hello,
I have a linked server to oracle 7.1 from SQL 2000. when I try to
execute simple select statement which returns one row of data using
openquery is not fetching the data. After 30 minutes SQL Query
analyser is hanging. This is happening on the production server and it
is working from last 6 months. I have tried by deleting and recreating
the linked server, but no use. Please help

Thanks,

Regards,
Pardhasaradhypardha.kolachina@.wipro.com (Pardhasaradhy) wrote in message news:<6be1c03a.0407192205.5b550f82@.posting.google.com>...
> Hello,
> I have a linked server to oracle 7.1 from SQL 2000. when I try to
> execute simple select statement which returns one row of data using
> openquery is not fetching the data. After 30 minutes SQL Query
> analyser is hanging. This is happening on the production server and it
> is working from last 6 months. I have tried by deleting and recreating
> the linked server, but no use. Please help
> Thanks,
> Regards,
> Pardhasaradhy

Have you tried connecting directly from the MSSQL server to the Oracle
server using SQLPlus? If that doesn't work, then the linked server
won't work either. If that does work, then you might want to check if
anything has been upgraded or installed recently, eg. if you upgraded
MDAC, then you may need to make registry changes, depending on the
Oracle version:

http://support.microsoft.com/default.aspx?scid=280106
http://support.microsoft.com/default.aspx?kbid=259959

If none of this helps, then perhaps you can give more information -
which MSSQL edition and servicepack, which version of MDAC and the
Oracle client software, which driver (ODBC, MS OLEDB, Oracle OLEDB)
you're using for the linked server etc.

Simon|||Hello,
Thanks for your reply.
The query is executing from SQL Plus and there are no updations on
the server. Following are the details you asked
for

Edition: SQL server 2000 8.00.760 Enterprise Edition
Service pack: SP3
MDAC version: 2.7 SP1
Oracle client s/w: Oracle 8i
I am using MS OLEDB PROVIDER for Oracle to connect to Oracle.

Thanks & Regards,
Pardhasaradhy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

server to Oracle

Hi,
I have an Oracle (8.1) & a SQL Server 2000 database with
Production data. There are situations when I need data from both the
databases. My first choice was to link Oracle to SQL and run DTS
overnight. But this would have a 1 day latency not to mention the time
it would take.

1. Has any one tried real time access via Linked server to Oracle?
How good is the performance?

2. The Oracle db is fairly big, so I'm kinda not in favor of
copying the whole thing over into SQL overnight. Is there an easier
way to just get only the changed records from Oracle?

3. Is there a better solution to this?

4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
change the SQL that you pass it? e.g. can the query accept a
parameter?

Thanks in advance
SudheshSudhesh Nayak wrote:

> Hi,
> I have an Oracle (8.1) & a SQL Server 2000 database with
> Production data. There are situations when I need data from both the
> databases. My first choice was to link Oracle to SQL and run DTS
> overnight. But this would have a 1 day latency not to mention the time
> it would take.
> 1. Has any one tried real time access via Linked server to Oracle?
> How good is the performance?
> 2. The Oracle db is fairly big, so I'm kinda not in favor of
> copying the whole thing over into SQL overnight. Is there an easier
> way to just get only the changed records from Oracle?
> 3. Is there a better solution to this?
> 4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
> change the SQL that you pass it? e.g. can the query accept a
> parameter?
> Thanks in advance
> Sudhesh

Why not use an Oracle database link for real-time access to SQL Server?
Also look at Oracle's SQL Server Transparent Gateway for accessing SQL
Server with PL/SQL.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||I need to go from SQL to Oracle as the Oracle db is owned by a
different group within the company... We have more (better) access to
the SQL box...

Thanks for the suggestion though
Sudhesh

Daniel Morgan <damorgan@.x.washington.edu> wrote in message news:<1072723371.798845@.yasure>...
> Sudhesh Nayak wrote:
> > Hi,
> > I have an Oracle (8.1) & a SQL Server 2000 database with
> > Production data. There are situations when I need data from both the
> > databases. My first choice was to link Oracle to SQL and run DTS
> > overnight. But this would have a 1 day latency not to mention the time
> > it would take.
> > 1. Has any one tried real time access via Linked server to Oracle?
> > How good is the performance?
> > 2. The Oracle db is fairly big, so I'm kinda not in favor of
> > copying the whole thing over into SQL overnight. Is there an easier
> > way to just get only the changed records from Oracle?
> > 3. Is there a better solution to this?
> > 4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
> > change the SQL that you pass it? e.g. can the query accept a
> > parameter?
> > Thanks in advance
> > Sudhesh
> Why not use an Oracle database link for real-time access to SQL Server?
> Also look at Oracle's SQL Server Transparent Gateway for accessing SQL
> Server with PL/SQL.|||Sudhesh Nayak wrote:
> I need to go from SQL to Oracle as the Oracle db is owned by a
> different group within the company... We have more (better) access to
> the SQL box...
> Thanks for the suggestion though
> Sudhesh

Then I'd suggest you talk to the Oracle group. They will likely not
allow you to access their database by whatever means you choose but
their DBAs will likely have strong conditions they will want to place
you your activities: I know I would.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||Can Oracle 8 act as an OLE DB provider to SQL Server as consumer?
In this case there is really no difference between using SQL Server as a
base through OLE DB vs. Oracle through transparent gateway, other than
that of control.

Just my two cents
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||Serge Rielau wrote:
> Can Oracle 8 act as an OLE DB provider to SQL Server as consumer?
> In this case there is really no difference between using SQL Server as a
> base through OLE DB vs. Oracle through transparent gateway, other than
> that of control.
> Just my two cents
> Serge

Difference is that with the Transparent Gateway and Oracle Developer
could issue PL/SQL against the SQL Server database rather than Transact
SQL. Cuts down on a lot of training and makes it possible to write
single SQL statements that simultaneously hit both back-ends.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||One thing you have to watch out for, I don't think Oracle 8.1 supports
the SQL-92 join syntax, IOW you have to do the joins in the where
clause.

Another developer at my company is using DTS and I am not impressed
with the results. I prefer to code whatever ETL I want to do myself,
that way I have a lot more control and much better oversight... when
you use DTS if you want to see what it's doing you have to open up the
packages and click on all the little icons etc, and I don't think
there's any way to step through to debug either.

On 29 Dec 2003 09:44:22 -0800, Sudhesh@.mail.com (Sudhesh Nayak) wrote:

>Hi,
> I have an Oracle (8.1) & a SQL Server 2000 database with
>Production data. There are situations when I need data from both the
>databases. My first choice was to link Oracle to SQL and run DTS
>overnight. But this would have a 1 day latency not to mention the time
>it would take.
> 1. Has any one tried real time access via Linked server to Oracle?
>How good is the performance?
> 2. The Oracle db is fairly big, so I'm kinda not in favor of
>copying the whole thing over into SQL overnight. Is there an easier
>way to just get only the changed records from Oracle?
> 3. Is there a better solution to this?
> 4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
>change the SQL that you pass it? e.g. can the query accept a
>parameter?
>Thanks in advance
>Sudhesh|||Daniel,

It seems the original poster is sitting on SQL Server. The Oracle DBMS
belongs to "another Group". So the argumnet of traing goes teh other way
around.
I don't know SQL Server well enough, but I'd be surprised if they don't
support OLE Table Functions which then, of course, allows T-SQL to hit
multiple sources at the same time, just like transparent gateway does.
The really interesting question is: How much optimization does
transparent gateway provide (in Oracle 8)? Only if it supports
distributed optimization with subquery pushdown to SQL Server will there
be any conceptual difference since OLE does not provide such
capabilities. Judging by the whitepapers available so far it seems some
of this heterogeneous optimization is coming in Oracle 10g, but that
isn't what the poster has.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||Serge Rielau wrote:
> Daniel,
> It seems the original poster is sitting on SQL Server. The Oracle DBMS
> belongs to "another Group". So the argumnet of traing goes teh other way
> around.
> I don't know SQL Server well enough, but I'd be surprised if they don't
> support OLE Table Functions which then, of course, allows T-SQL to hit
> multiple sources at the same time, just like transparent gateway does.
> The really interesting question is: How much optimization does
> transparent gateway provide (in Oracle 8)? Only if it supports
> distributed optimization with subquery pushdown to SQL Server will there
> be any conceptual difference since OLE does not provide such
> capabilities. Judging by the whitepapers available so far it seems some
> of this heterogeneous optimization is coming in Oracle 10g, but that
> isn't what the poster has.
> Cheers
> Serge

I'm not sure why you specify Oracle 8 as is it over a decade old. Could
be 8i, could be 9i, and it is essential the OP find out. If the SQL must
be executed on the SQL Server side, as has since been indiated,
Transparent Gateway is out. My suggestion is that the OP work out the
solution with the Oracle DBA responsible for the database. It is likely
they will not say "Yes" to whatever is proposed but will have very
specific ideas and concerns.

You are correct about 10g. I've been working with it for months and it
is quite a different animal.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||Here is how the thread started...
"I have an Oracle (8.1) & a SQL Server 2000 database with Production data."

Larry won't give me Oracle 10g early I'm affraid. But that's OK :-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||Serge Rielau wrote:

> Here is how the thread started...
> "I have an Oracle (8.1) & a SQL Server 2000 database with Production data."
> Larry won't give me Oracle 10g early I'm affraid. But that's OK :-)
> Cheers
> Serge

You can get the 10g JDeveloper now if you want.

> I have an Oracle (8.1) & a SQL Server 2000 database with
> Production data. There are situations when I need data from both the
> databases. My first choice was to link Oracle to SQL and run DTS
> overnight. But this would have a 1 day latency not to mention the time
> it would take.

OK, what I would do here is ask the Oracle people for read only access
to their db - I would also request that they time-stamp their records
(or at least the ones you're interested in).

You can then write a programme in the language of your choice to act as
a service every night to go and get the records in the Oracle database
that are new - you could even run this service every 5 minutes or so if
the Oracle people let you - the advantage here is that you will only be
drawing down relatively small amounts of data if you do it regularly.

> 2. The Oracle db is fairly big, so I'm kinda not in favor of

What does "big" actually mean in MB? How much of this data do you need?

Paul...

> Sudhesh

--
plinehan y_a_h_o_o and d_o_t com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.

"XML avoids the fundamental question of what we should do,
by focusing entirely on how we should do it."

quote from http://www.metatorial.com|||Sudhesh,
my company has a product, MetaMatrix, that will link the two in exactly the
manner you require. It will also combine the Oracle & MS SQLServer schemas &
as a unified virtual database make them both together look like an instance
of your MSSQLServer database - then you can get all data together.

Regards
David Penney
http://www.metamatrix.com

"Sudhesh Nayak" <Sudhesh@.mail.com> wrote in message
news:8d85e256.0312290944.26fc9878@.posting.google.c om...
> Hi,
> I have an Oracle (8.1) & a SQL Server 2000 database with
> Production data. There are situations when I need data from both the
> databases. My first choice was to link Oracle to SQL and run DTS
> overnight. But this would have a 1 day latency not to mention the time
> it would take.
> 1. Has any one tried real time access via Linked server to Oracle?
> How good is the performance?
> 2. The Oracle db is fairly big, so I'm kinda not in favor of
> copying the whole thing over into SQL overnight. Is there an easier
> way to just get only the changed records from Oracle?
> 3. Is there a better solution to this?
> 4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
> change the SQL that you pass it? e.g. can the query accept a
> parameter?
> Thanks in advance
> Sudhesh|||On Sun, 1 Feb 2004 19:35:10 +0000 (UTC), "David Penney"
<anon@.noone.com> wrote:

>Sudhesh,
>my company has a product, MetaMatrix, that will link the two in exactly the
>manner you require. It will also combine the Oracle & MS SQLServer schemas &
>as a unified virtual database make them both together look like an instance
>of your MSSQLServer database - then you can get all data together.
>Regards
> David Penney
> http://www.metamatrix.com
>"Sudhesh Nayak" <Sudhesh@.mail.com> wrote in message
>news:8d85e256.0312290944.26fc9878@.posting.google.c om...
>> Hi,
>> I have an Oracle (8.1) & a SQL Server 2000 database with
>> Production data. There are situations when I need data from both the
>> databases. My first choice was to link Oracle to SQL and run DTS
>> overnight. But this would have a 1 day latency not to mention the time
>> it would take.
>>
>> 1. Has any one tried real time access via Linked server to Oracle?
>> How good is the performance?
>>
>> 2. The Oracle db is fairly big, so I'm kinda not in favor of
>> copying the whole thing over into SQL overnight. Is there an easier
>> way to just get only the changed records from Oracle?
>>
>> 3. Is there a better solution to this?
>>
>> 4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
>> change the SQL that you pass it? e.g. can the query accept a
>> parameter?
>>
>> Thanks in advance
>> Sudhesh
I'm doings this. The main problem I had was wanting to relate data in
Oracle to that in SQL Server. It was horribly slow and we established
that what was happening was that MSSQL was going to pull the whole
table over from Oracle and then execute the query.

I solved using openquery. I created tables in SQLServer to hold what I
wanted from Oracle, populate using openquery and then deleted it.
Openquery performance is fine. You can use parameters, but you do it
by building up a string, containing the openquery statement and the
actual query and then you exec the string. This involves a nice game
of getting the right number of quotes!

A couple of other gotchas;

The oracle table names and columns have to be in capitals.
The oledb driver doesn't like columns defined as number, it wants them
to be NUMBER(12,0) or whatever. (The error is that the schema has
changed between parse and execution, or words to that effect).

Depending on your exact setup you may not find these problems, but we
did.

If you want a sample post a reply, as I'm not at work right now.|||Lyndon
thats one way to go - MetaMatrix does it a faster way without having the
overhead to write then read the data into the second database to do a join.
ts also quicker to deliver because the operatioin is specified in a
graphical modeling tool. Its also quicker to maintain than the soluution you
found. The product also transforms the data into a diffeerent datamodel &
can do reads & writes. Effectively the two databases are federated into a
Virtual Database with its own specific schema.

However if your solution is fast enough & a single limited case seems a good
solution for you, for more complex requirements its not optimal.

Regards,
Davd

"Lyndon Hills" <lyndon@.nospam.tenegi.com> wrote in message
news:dset109u84kjgq7fuldfk7olvs9mqchlt4@.4ax.com...
> On Sun, 1 Feb 2004 19:35:10 +0000 (UTC), "David Penney"
> <anon@.noone.com> wrote:
> >Sudhesh,
> >my company has a product, MetaMatrix, that will link the two in exactly
the
> >manner you require. It will also combine the Oracle & MS SQLServer
schemas &
> >as a unified virtual database make them both together look like an
instance
> >of your MSSQLServer database - then you can get all data together.
> >Regards
> > David Penney
> > http://www.metamatrix.com
> >"Sudhesh Nayak" <Sudhesh@.mail.com> wrote in message
> >news:8d85e256.0312290944.26fc9878@.posting.google.c om...
> >> Hi,
> >> I have an Oracle (8.1) & a SQL Server 2000 database with
> >> Production data. There are situations when I need data from both the
> >> databases. My first choice was to link Oracle to SQL and run DTS
> >> overnight. But this would have a 1 day latency not to mention the time
> >> it would take.
> >>
> >> 1. Has any one tried real time access via Linked server to Oracle?
> >> How good is the performance?
> >>
> >> 2. The Oracle db is fairly big, so I'm kinda not in favor of
> >> copying the whole thing over into SQL overnight. Is there an easier
> >> way to just get only the changed records from Oracle?
> >>
> >> 3. Is there a better solution to this?
> >>
> >> 4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamically
> >> change the SQL that you pass it? e.g. can the query accept a
> >> parameter?
> >>
> >> Thanks in advance
> >> Sudhesh
> I'm doings this. The main problem I had was wanting to relate data in
> Oracle to that in SQL Server. It was horribly slow and we established
> that what was happening was that MSSQL was going to pull the whole
> table over from Oracle and then execute the query.
> I solved using openquery. I created tables in SQLServer to hold what I
> wanted from Oracle, populate using openquery and then deleted it.
> Openquery performance is fine. You can use parameters, but you do it
> by building up a string, containing the openquery statement and the
> actual query and then you exec the string. This involves a nice game
> of getting the right number of quotes!
> A couple of other gotchas;
> The oracle table names and columns have to be in capitals.
> The oledb driver doesn't like columns defined as number, it wants them
> to be NUMBER(12,0) or whatever. (The error is that the schema has
> changed between parse and execution, or words to that effect).
> Depending on your exact setup you may not find these problems, but we
> did.
> If you want a sample post a reply, as I'm not at work right now.

server to Oracle

I'm trying to pass a query from sql server 2000 to Oracle using linked
servers. I don't want to use DTS. While it's easy enough to use OPENQUERY
to pass thru a query that returns a dataset, I can't seem to pass thru a
query that doesn't return a dataset eg a create table query or a drop table
query. If I try, I get an error saying that the query returns no columns.
Is it possible to pass thru a query that returns no columns using a linked
server?
For example, the following query:
select *
from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
returns the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
OLE DB provider 'MSDAORA' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='MSDAORA', Query=CREATE
TABLE MYTABLE AS SELECT * FROM EMP'].arch (Sorry , cannot test it right now)
SELECT *
FROM
OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
If it does not work you may want to try
CREATE FUNCTION dbo.fn_getdata()
AS
RETURNS TABLE
AS
BEGIN
RETURN(
SELECT *
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
END
"arch" <archangel@.arach.net.au> wrote in message
news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au...
> I'm trying to pass a query from sql server 2000 to Oracle using linked
> servers. I don't want to use DTS. While it's easy enough to use
> OPENQUERY
> to pass thru a query that returns a dataset, I can't seem to pass thru a
> query that doesn't return a dataset eg a create table query or a drop
> table
> query. If I try, I get an error saying that the query returns no columns.
> Is it possible to pass thru a query that returns no columns using a linked
> server?
> For example, the following query:
> select *
> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
> returns the error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
> OLE DB provider 'MSDAORA' indicates that the object has no columns.
> OLE DB error trace [Non-interface error: OLE DB provider unable to
> process
> object, since the object has no columnsProviderName='MSDAORA',
> Query=CREATE
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>|||No luck with any of that.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23A$6awKGHA.2628@.TK2MSFTNGP15.phx.gbl...
> arch (Sorry , cannot test it right now)
> SELECT *
> FROM
> OPENQUERY(ORA8I,'SELECT * INTO MYTABLE FROM EMP')
> If it does not work you may want to try
> CREATE FUNCTION dbo.fn_getdata()
> AS
> RETURNS TABLE
> AS
> BEGIN
> RETURN(
> SELECT *
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> SELECT * INTO database.MyTable FROM DataBase.EMP;') AS O)
> END
>
>
> "arch" <archangel@.arach.net.au> wrote in message
> news:newscache$rzf9ui$m9c$1@.phantom.amnet.net.au...
>> I'm trying to pass a query from sql server 2000 to Oracle using linked
>> servers. I don't want to use DTS. While it's easy enough to use
>> OPENQUERY
>> to pass thru a query that returns a dataset, I can't seem to pass thru a
>> query that doesn't return a dataset eg a create table query or a drop
>> table
>> query. If I try, I get an error saying that the query returns no
>> columns.
>> Is it possible to pass thru a query that returns no columns using a
>> linked
>> server?
>> For example, the following query:
>> select *
>> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
>> returns the error:
>> Server: Msg 7357, Level 16, State 2, Line 1
>> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
>> OLE DB provider 'MSDAORA' indicates that the object has no columns.
>> OLE DB error trace [Non-interface error: OLE DB provider unable to
>> process
>> object, since the object has no columnsProviderName='MSDAORA',
>> Query=CREATE
>> TABLE MYTABLE AS SELECT * FROM EMP'].
>>
>

server to Oracle

Hi, I am developing DTS that extract data from Oracle via
Linked server. I'm using MS OLE DB for Oracle.
The following error message prompted when I query to
selected tables (12 tables out of 40+ tables) in Oracle.
Server: Msg 7317, Level 16, State 1, Line 1 OLE DB
provider 'MSDAORA' returned an invalid schema definition.
OLE DB error trace [Non-interface error: OLE/DB provider
returned an invalid schema definition.].One scenario where you can get the error is with data types
that aren't supported by the OLE DB provider or by SQL
Server.
Can you query the tables using just an Openquery in Query
Analyzer?
You would probably want to check the documentation for the
driver to determine what data types are supported. Also,
make sure you are using the latest provider, Oracle client.
-Sue
On Mon, 23 Aug 2004 10:10:22 -0700, "James"
<james@.silverglobe.com> wrote:

>Hi, I am developing DTS that extract data from Oracle via
>Linked server. I'm using MS OLE DB for Oracle.
>The following error message prompted when I query to
>selected tables (12 tables out of 40+ tables) in Oracle.
>Server: Msg 7317, Level 16, State 1, Line 1 OLE DB
>provider 'MSDAORA' returned an invalid schema definition.
>OLE DB error trace [Non-interface error: OLE/DB provider
>returned an invalid schema definition.].|||Hi James,
Thanks for using MSDN Managed Newsgroup!
Thanks the perfect answer from Sue!
From your descriptions, I understood you meet the error 7317 when using DTS
push data from Oracle. Have I understood you? If there is anything I
misunderstood, please feel free to let me know.
Bbased on my knowledge, you may have encounter an known issue in MDAC. The
provider asks Oracle for a list of the column names in an Oracle index. For
most indexes the internal name Oracle has for the index columns is the same
as the actual column names. However, if the Oracle index is descending or
is a function_based index, it does not return actual column names, it
returns a generated name of some sort. Our Oracle provider does not
distinguish between the results and it tries to use the returned values as
actual column names, resulting in the "invalid schema definition". I cannot
tell for sure from the internal documentation, but this behavior may have
changed between versions on the Oracle side. Our Oracle provider has not
been updated recently, to take advantage of newer Oracle functionality you
need to use Oracle's provider instead of ours.
You'd better use four-part name syntax correct this issue. Use the query
like this
SELECT * FROM OPENQUERY(WACRPPRD, 'select
LAST_NAME,FIRST_NAME,PH_NUM,PAGER,DIRECT
_PHONE,EXTENSION,EMAIL_ADDR,TITLE,DE
PT_DESC,
OFFICE_SITE,SUPV_PH_NUM from PH.QBS_PH_PEOPLE where PH_NUM is not null and
ACTIVE_FLAG = ''Y'' order by QPE_EMP_NUM')
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Sue and Mingqing,
Thanks for the promptly reply.
I tried using open query and it works. Thus, I assume is
the version incompatibility issue. Will check it out
tomorrow and post another message for the benefit of the
others.
rgrds,
James Gan HJ

>--Original Message--
>Hi James,
>Thanks for using MSDN Managed Newsgroup!
>Thanks the perfect answer from Sue!
>From your descriptions, I understood you meet the error
7317 when using DTS
>push data from Oracle. Have I understood you? If there is
anything I
>misunderstood, please feel free to let me know.
>Bbased on my knowledge, you may have encounter an known
issue in MDAC. The
>provider asks Oracle for a list of the column names in an
Oracle index. For
>most indexes the internal name Oracle has for the index
columns is the same
>as the actual column names. However, if the Oracle index
is descending or
>is a function_based index, it does not return actual
column names, it
>returns a generated name of some sort. Our Oracle
provider does not
>distinguish between the results and it tries to use the
returned values as
>actual column names, resulting in the "invalid schema
definition". I cannot
>tell for sure from the internal documentation, but this
behavior may have
>changed between versions on the Oracle side. Our Oracle
provider has not
>been updated recently, to take advantage of newer Oracle
functionality you
>need to use Oracle's provider instead of ours.
>You'd better use four-part name syntax correct this
issue. Use the query
>like this
>SELECT * FROM OPENQUERY(WACRPPRD, 'select
> LAST_NAME,FIRST_NAME,PH_NUM,PAGER,DIRECT
_PHONE,EXTENSION,E
MAIL_ADDR,TITLE,DE
>PT_DESC,
>OFFICE_SITE,SUPV_PH_NUM from PH.QBS_PH_PEOPLE where
PH_NUM is not null and
>ACTIVE_FLAG = ''Y'' order by QPE_EMP_NUM')
>Thank you for your patience and cooperation. If you have
any questions or
>concerns, don't hesitate to let me know. We are here to
be of assistance!
>
>Sincerely yours,
>Mingqing Cheng
>Microsoft Developer Community Support
>----
--
>Introduction to Yukon! -
http://www.microsoft.com/sql/yukon
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only, many thanks!
>
>
>
>
>.
>|||Hi, here's my spec.
Oracle server = Oracle Server 8i release 8.1.7.4
SQL Server which I need to establish a linked server connection=
OLE DB or Oracle version 2.71.9030
Oracle SQL Plus Release 9.2.0.1.0
Does this mean that I need to use the Oracle SQL client v 8?
I tried the latest MDAC with OLE DB for Oracle 2.8, it doesn't work as well.
rgrds,
James Gan HJ
""Mingqing Cheng [MSFT]"" wrote:

> Hi James,
> Thanks for using MSDN Managed Newsgroup!
> Thanks the perfect answer from Sue!
> From your descriptions, I understood you meet the error 7317 when using DT
S
> push data from Oracle. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know.
> Bbased on my knowledge, you may have encounter an known issue in MDAC. The
> provider asks Oracle for a list of the column names in an Oracle index. Fo
r
> most indexes the internal name Oracle has for the index columns is the sam
e
> as the actual column names. However, if the Oracle index is descending or
> is a function_based index, it does not return actual column names, it
> returns a generated name of some sort. Our Oracle provider does not
> distinguish between the results and it tries to use the returned values as
> actual column names, resulting in the "invalid schema definition". I canno
t
> tell for sure from the internal documentation, but this behavior may have
> changed between versions on the Oracle side. Our Oracle provider has not
> been updated recently, to take advantage of newer Oracle functionality you
> need to use Oracle's provider instead of ours.
> You'd better use four-part name syntax correct this issue. Use the query
> like this
> SELECT * FROM OPENQUERY(WACRPPRD, 'select
> LAST_NAME,FIRST_NAME,PH_NUM,PAGER,DIRECT
_PHONE,EXTENSION,EMAIL_ADDR,TITLE,
DE
> PT_DESC,
> OFFICE_SITE,SUPV_PH_NUM from PH.QBS_PH_PEOPLE where PH_NUM is not null and
> ACTIVE_FLAG = ''Y'' order by QPE_EMP_NUM')
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>
>
>|||No, you don't necessarily need to go back to the v 8 Client.
What task are you using and how are you querying the Oracle
database in the task? When you hit data type issues,
sometimes it's better to just use pass-through queries with
Openquery as long as that works. It's generally faster
against an Oracle linked server anyway.
-Sue
On Mon, 30 Aug 2004 00:29:02 -0700, "James"
<James@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi, here's my spec.
>Oracle server = Oracle Server 8i release 8.1.7.4
>SQL Server which I need to establish a linked server connection=
>OLE DB or Oracle version 2.71.9030
>Oracle SQL Plus Release 9.2.0.1.0
>Does this mean that I need to use the Oracle SQL client v 8?
>I tried the latest MDAC with OLE DB for Oracle 2.8, it doesn't work as well
.
>rgrds,
>James Gan HJ
>
>""Mingqing Cheng [MSFT]"" wrote:
>|||Yep, open query works. Thanks.
So, the rule of thumb is to always use open query?
"Sue Hoegemeier" wrote:

> No, you don't necessarily need to go back to the v 8 Client.
> What task are you using and how are you querying the Oracle
> database in the task? When you hit data type issues,
> sometimes it's better to just use pass-through queries with
> Openquery as long as that works. It's generally faster
> against an Oracle linked server anyway.
> -Sue
> On Mon, 30 Aug 2004 00:29:02 -0700, "James"
> <James@.discussions.microsoft.com> wrote:
>
>|||Not necessarily but in your case it seems appropriate and
openquery will generally be faster - especially with Oracle.
-Sue
On Tue, 31 Aug 2004 03:35:08 -0700, "James"
<James@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Yep, open query works. Thanks.
>So, the rule of thumb is to always use open query?
>
>"Sue Hoegemeier" wrote:
>

Friday, February 24, 2012

server to Pervasive 8 - Connection Error

server to oracle9i

server to oracle9i

server to oracle9i

server to Oracle via ODBC, return less records

server to Oracle via ODBC, return less records

server to Oracle using SQL Server 2005

server to Oracle using SQL Server 2005

server to Oracle using SQL Server 2005

server to Oracle on Unix

server to Oracle on Unix

server to Oracle -OLE/DB Provider 'MSDAORA' IDBInitialize

server to Oracle doesnt fetch correct values

server to oracle doesn't fetch correct datavalues

server to oracle doesn't fetch correct datavalues

server To Oracle Causes SQL Server Instance to Crash

server to Oracle 9i

server to Oracle 9i

server to Oracle 9i

server to Oracle 9.2 database

server to Oracle 9.2 database

server to Oracle 9.2 database

server to Oracle 9.2

server to Oracle 9.2

server to Oracle - error on NVARCHAR2()

server to Oracle

server to Oracle

server to oracle

server to Oracle

server to Oracle

server to Oracle

linking two datasets

Blog Archive

About Me