Oracle Server connecting to a Microsoft SQL Server

Hello everyone,
I am new to this so I need a lot of help. We have an
Oracle server. From the server I need to connect to a
Microsoft SQL Server. We can currently connect to other
Oracle servers by creating a Database Link. We used
Oracle Enterprise Manager to create the database links
and updated the TNSNAMES.ora file on the server. By
trying to do the same thing for the SQL server, we are
unsuccessful. Could someone please help us? Please
remember we are new to this process.
Thank you!Read the section 'Managing Clients' under 'Administering
SQL Server' in the SQL2000 Books Online.
Linchi

quote:

---

>--Original Message--
>Hello everyone,
>I am new to this so I need a lot of help. We have an
>Oracle server. From the server I need to connect to a
>Microsoft SQL Server. We can currently connect to other
>Oracle servers by creating a Database Link. We used
>Oracle Enterprise Manager to create the database links
>and updated the TNSNAMES.ora file on the server. By
>trying to do the same thing for the SQL server, we are
>unsuccessful. Could someone please help us? Please
>remember we are new to this process.
>Thank you!
>.
>

---

Oracle report returning strange data

I have a RS report that is connecting to an Oracle Database (8.1.7), the
8.1.7 oracle client is installed on RS Sever, and the result set is returning
strange data. I'm using a simple view that was created in the Oracle
database, that returns a small record set about 100 records. I'm seeing ASCII
values in place of some data, in the Data tab and the Preview tab and the
Published report.
I have tried using both Oracle Drivers and the Microsoft ODBC drivers for
Oracle, and I get the same results.
Some of the data looks just fine but some of the characters get replaced by
ASCII values.
I have installed RS SP1.
Don't know what I am doing wrong...Hi Gary
We are running SQL Reporting Services (SP1) against an Oracle 9i database
without any problems. In our case 9.2.0.4 client is used to talk with
9.2.0.4 database.
I suspect the problem you are having is related to version of Oracle client
you are using. If you have access to a client 9.2.0.4 or higher you may want
to give that a try.
"garyc" wrote:
> I have a RS report that is connecting to an Oracle Database (8.1.7), the
> 8.1.7 oracle client is installed on RS Sever, and the result set is returning
> strange data. I'm using a simple view that was created in the Oracle
> database, that returns a small record set about 100 records. I'm seeing ASCII
> values in place of some data, in the Data tab and the Preview tab and the
> Published report.
> I have tried using both Oracle Drivers and the Microsoft ODBC drivers for
> Oracle, and I get the same results.
> Some of the data looks just fine but some of the characters get replaced by
> ASCII values.
> I have installed RS SP1.
> Don't know what I am doing wrong...

Oracle linked Server invalid Book mark error

Hi,
I am connecting oracle through the linked server from SQL server
2000.I am able to access oracle data using OPENQUERY method. But Whenever i
Use a direct quiery say
select count(*) from linkedserver.schema.tableName i receive an error
message like this.
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED'
index 'TABLE NAME_IX02' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an
invalid bookmark ordinal from the index rowset.].You can get the error if you created the linked server using
the provider options Index As Access Path.
Try removing that option.
-Sue
On Fri, 8 Sep 2006 09:16:01 -0700, Suriya Prakash <Suriya
Prakash@.discussions.microsoft.com> wrote:

>Hi,
> I am connecting oracle through the linked server from SQL server
>2000.I am able to access oracle data using OPENQUERY method. But Whenever i
>Use a direct quiery say
>select count(*) from linkedserver.schema.tableName i receive an error
>message like this.
>OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED'
>index 'TABLE NAME_IX02' with incorrect bookmark ordinal 0.
>OLE DB error trace [Non-interface error: OLE/DB provider returned an
>invalid bookmark ordinal from the index rowset.].
>|||Hi Sue,
When I create a new linked server by removing this option. It
works.
Thanks.
-Suriya
"Sue Hoegemeier" wrote:

> You can get the error if you created the linked server using
> the provider options Index As Access Path.
> Try removing that option.
> -Sue
> On Fri, 8 Sep 2006 09:16:01 -0700, Suriya Prakash <Suriya
> Prakash@.discussions.microsoft.com> wrote:
>
>|||Your welcome - thanks for posting back that it worked!
-Sue
On Mon, 11 Sep 2006 10:12:02 -0700, Suriya Prakash
<SuriyaPrakash@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue,
> When I create a new linked server by removing this option. It
>works.
>Thanks.
>-Suriya
>
>"Sue Hoegemeier" wrote:
>

Oracle linked Server invalid Book mark error

Hi,
I am connecting oracle through the linked server from SQL server
2000.I am able to access oracle data using OPENQUERY method. But Whenever i
Use a direct quiery say
select count(*) from linkedserver.schema.tableName i receive an error
message like this.
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED'
index 'TABLE NAME_IX02' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an
invalid bookmark ordinal from the index rowset.].You can get the error if you created the linked server using
the provider options Index As Access Path.
Try removing that option.
-Sue
On Fri, 8 Sep 2006 09:16:01 -0700, Suriya Prakash <Suriya
Prakash@.discussions.microsoft.com> wrote:
>Hi,
> I am connecting oracle through the linked server from SQL server
>2000.I am able to access oracle data using OPENQUERY method. But Whenever i
>Use a direct quiery say
>select count(*) from linkedserver.schema.tableName i receive an error
>message like this.
>OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED'
>index 'TABLE NAME_IX02' with incorrect bookmark ordinal 0.
>OLE DB error trace [Non-interface error: OLE/DB provider returned an
>invalid bookmark ordinal from the index rowset.].
>|||Hi Sue,
When I create a new linked server by removing this option. It
works.
Thanks.
-Suriya
"Sue Hoegemeier" wrote:
> You can get the error if you created the linked server using
> the provider options Index As Access Path.
> Try removing that option.
> -Sue
> On Fri, 8 Sep 2006 09:16:01 -0700, Suriya Prakash <Suriya
> Prakash@.discussions.microsoft.com> wrote:
> >Hi,
> > I am connecting oracle through the linked server from SQL server
> >2000.I am able to access oracle data using OPENQUERY method. But Whenever i
> >Use a direct quiery say
> >select count(*) from linkedserver.schema.tableName i receive an error
> >message like this.
> >
> >OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED'
> >index 'TABLE NAME_IX02' with incorrect bookmark ordinal 0.
> >
> >OLE DB error trace [Non-interface error: OLE/DB provider returned an
> >invalid bookmark ordinal from the index rowset.].
> >
> >
>|||Your welcome - thanks for posting back that it worked!
-Sue
On Mon, 11 Sep 2006 10:12:02 -0700, Suriya Prakash
<SuriyaPrakash@.discussions.microsoft.com> wrote:
>Hi Sue,
> When I create a new linked server by removing this option. It
>works.
>Thanks.
>-Suriya
>
>"Sue Hoegemeier" wrote:
>> You can get the error if you created the linked server using
>> the provider options Index As Access Path.
>> Try removing that option.
>> -Sue
>> On Fri, 8 Sep 2006 09:16:01 -0700, Suriya Prakash <Suriya
>> Prakash@.discussions.microsoft.com> wrote:
>> >Hi,
>> > I am connecting oracle through the linked server from SQL server
>> >2000.I am able to access oracle data using OPENQUERY method. But Whenever i
>> >Use a direct quiery say
>> >select count(*) from linkedserver.schema.tableName i receive an error
>> >message like this.
>> >
>> >OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED'
>> >index 'TABLE NAME_IX02' with incorrect bookmark ordinal 0.
>> >
>> >OLE DB error trace [Non-interface error: OLE/DB provider returned an
>> >invalid bookmark ordinal from the index rowset.].
>> >
>> >
>>

Oracle linked server

Hello,
I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
database.
I've set up the linked server and necessary permissions and it has been
working successfully for months. However occasionally after rebooting the
machine I receive this message when attempting a query from the linked
server:
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components
were not found. These components are supplied by Oracle Corporation and are
part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
I can still connect successfully through either Oracle Net Configuration
Assistant or an ODBC DSN and the only way I know how to solve this is by
stopping and restarting the SQL Server instance. The only thing I can think
of is that during server startup, the SQL Service gets started before the
oracle service starts, and somehow SQL caches this connection.
Has anyone experienced this? If so, what's the best way to deal with this
without stopping the server?
Thank you,
Aaron Lowe
In some similar situations, reinstalling the Oracle client
on the server when logged in using the service account has
fixed this issue.
-Sue
On Tue, 12 Apr 2005 10:28:46 -0500, "Aaron M. Lowe"
<alowe@.uic.edu> wrote:

>Hello,
>I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
>database.
>I've set up the linked server and necessary permissions and it has been
>working successfully for months. However occasionally after rebooting the
>machine I receive this message when attempting a query from the linked
>server:
>Server: Msg 7399, Level 16, State 1, Line 3
>OLE DB provider 'MSDAORA' reported an error.
>[OLE/DB provider returned message: Oracle client and networking components
>were not found. These components are supplied by Oracle Corporation and are
>part of the Oracle Version 7.3.3 or later client software installation.
>Provider is unable to function until these components are installed.]
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>I can still connect successfully through either Oracle Net Configuration
>Assistant or an ODBC DSN and the only way I know how to solve this is by
>stopping and restarting the SQL Server instance. The only thing I can think
>of is that during server startup, the SQL Service gets started before the
>oracle service starts, and somehow SQL caches this connection.
>Has anyone experienced this? If so, what's the best way to deal with this
>without stopping the server?
>Thank you,
>Aaron Lowe
>

Oracle linked server

Hello,
I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
database.
I've set up the linked server and necessary permissions and it has been
working successfully for months. However occasionally after rebooting the
machine I receive this message when attempting a query from the linked
server:
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking componen
ts
were not found. These components are supplied by Oracle Corporation and are
part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
I can still connect successfully through either Oracle Net Configuration
Assistant or an ODBC DSN and the only way I know how to solve this is by
stopping and restarting the SQL Server instance. The only thing I can think
of is that during server startup, the SQL Service gets started before the
oracle service starts, and somehow SQL caches this connection.
Has anyone experienced this? If so, what's the best way to deal with this
without stopping the server?
Thank you,
Aaron LoweIn some similar situations, reinstalling the Oracle client
on the server when logged in using the service account has
fixed this issue.
-Sue
On Tue, 12 Apr 2005 10:28:46 -0500, "Aaron M. Lowe"
<alowe@.uic.edu> wrote:

>Hello,
>I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
>database.
>I've set up the linked server and necessary permissions and it has been
>working successfully for months. However occasionally after rebooting the
>machine I receive this message when attempting a query from the linked
>server:
>Server: Msg 7399, Level 16, State 1, Line 3
>OLE DB provider 'MSDAORA' reported an error.
>[OLE/DB provider returned message: Oracle client and networking compone
nts
>were not found. These components are supplied by Oracle Corporation and are
>part of the Oracle Version 7.3.3 or later client software installation.
>Provider is unable to function until these components are installed.]
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>I can still connect successfully through either Oracle Net Configuration
>Assistant or an ODBC DSN and the only way I know how to solve this is by
>stopping and restarting the SQL Server instance. The only thing I can think
>of is that during server startup, the SQL Service gets started before the
>oracle service starts, and somehow SQL caches this connection.
>Has anyone experienced this? If so, what's the best way to deal with this
>without stopping the server?
>Thank you,
>Aaron Lowe
>