Problems with SQL Server 2005 Oracle Linked Server

I am experiencing numerous problems with a SQL Server 2005 Linked Server for
Oracle. The first and most pressing issue is when executing the following
statement:
Select * from openquery( {Oracle Linked Server}, 'select * from
target_table;')
Using the above query the results set in SSMS is only a subset of all the
rows that are actually in the table.
For example if there are 200 rows in the table, the above query will only
return 150 rows. Although, it should return all the rows in the table. At
the same time I can execute the following query and get the correct row
count:
Select * from openquery( {Oracle Linked Server}, 'select count(*) from
target_table;')
I am using Microsoft OLE DB Provider for ODBC Drivers with a data source set
up using ODBC Administrator. I have installed Oracle 9i client tools. I
have attempted a linked server using Oracle Provider for OLE DB to no avail.
The linked server using Oracle Provider for OLE DB returns the following
error:
OLE DB provider "OraOLEDB.Oracle" for linked server "{Linked Server}"
returned message "ORA-00911: invalid character".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select count(*) from
{Target_Table};" for execution against OLE DB provider "OraOLEDB.Oracle" for
linked server "{Linked Server}".
Any help with this issue would be greatly appreciated.
Thandi Turner"Problems w/ SQL2K5 Oracle Linked Server" <Problems w/ SQL2K5 Oracle Linked
Server@.discussions.microsoft.com> wrote in message
news:0F1603EE-0DEE-47B4-8F8A-40F16BE0FA6E@.microsoft.com...
>I am experiencing numerous problems with a SQL Server 2005 Linked Server
>for
> Oracle. The first and most pressing issue is when executing the
> following
> statement:
> Select * from openquery( {Oracle Linked Server}, 'select * from
> target_table;')
> Using the above query the results set in SSMS is only a subset of all the
> rows that are actually in the table.
> For example if there are 200 rows in the table, the above query will only
> return 150 rows. Although, it should return all the rows in the table.
> At
> the same time I can execute the following query and get the correct row
> count:
> Select * from openquery( {Oracle Linked Server}, 'select count(*) from
> target_table;')
> I am using Microsoft OLE DB Provider for ODBC Drivers with a data source
> set
> up using ODBC Administrator. I have installed Oracle 9i client tools. I
> have attempted a linked server using Oracle Provider for OLE DB to no
> avail.
> The linked server using Oracle Provider for OLE DB returns the following
> error:
> OLE DB provider "OraOLEDB.Oracle" for linked server "{Linked Server}"
> returned message "ORA-00911: invalid character".
> Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing the query "select count(*) from
> {Target_Table};" for execution against OLE DB provider "OraOLEDB.Oracle"
> for
> linked server "{Linked Server}".
>
You need to be using the OLE DB Provider from Oracle.
The error
ORA-00911: invalid character
means exactly what it says.
In Oracle SQL ';' is an invalid character. It is the statement terminiator
in PL/SQL, but is forbidden in Oracle SQL.
David|||Thanks, but removing the semi-colon eliminated the error but still did not
return the full result set. Still need help in getting all of the rows back
regardless of the provider.
"David Browne" wrote:
> "Problems w/ SQL2K5 Oracle Linked Server" <Problems w/ SQL2K5 Oracle Linked
> Server@.discussions.microsoft.com> wrote in message
> news:0F1603EE-0DEE-47B4-8F8A-40F16BE0FA6E@.microsoft.com...
> >I am experiencing numerous problems with a SQL Server 2005 Linked Server
> >for
> > Oracle. The first and most pressing issue is when executing the
> > following
> > statement:
> >
> > Select * from openquery( {Oracle Linked Server}, 'select * from
> > target_table;')
> >
> > Using the above query the results set in SSMS is only a subset of all the
> > rows that are actually in the table.
> >
> > For example if there are 200 rows in the table, the above query will only
> > return 150 rows. Although, it should return all the rows in the table.
> > At
> > the same time I can execute the following query and get the correct row
> > count:
> >
> > Select * from openquery( {Oracle Linked Server}, 'select count(*) from
> > target_table;')
> >
> > I am using Microsoft OLE DB Provider for ODBC Drivers with a data source
> > set
> > up using ODBC Administrator. I have installed Oracle 9i client tools. I
> > have attempted a linked server using Oracle Provider for OLE DB to no
> > avail.
> > The linked server using Oracle Provider for OLE DB returns the following
> > error:
> >
> > OLE DB provider "OraOLEDB.Oracle" for linked server "{Linked Server}"
> > returned message "ORA-00911: invalid character".
> > Msg 7321, Level 16, State 2, Line 1
> > An error occurred while preparing the query "select count(*) from
> > {Target_Table};" for execution against OLE DB provider "OraOLEDB.Oracle"
> > for
> > linked server "{Linked Server}".
> >
> You need to be using the OLE DB Provider from Oracle.
> The error
> ORA-00911: invalid character
> means exactly what it says.
> In Oracle SQL ';' is an invalid character. It is the statement terminiator
> in PL/SQL, but is forbidden in Oracle SQL.
> David
>
>|||"Problems w/ SQL2K5 Oracle Linked Server"
<ProblemswSQL2K5OracleLinkedServer@.discussions.microsoft.com> wrote in
message news:1E41CFD4-9A3E-461F-8B1D-2E3D7BF3F711@.microsoft.com...
> Thanks, but removing the semi-colon eliminated the error but still did not
> return the full result set. Still need help in getting all of the rows
> back
> regardless of the provider.
>
What version of the Oracle client and what version of Oracle?
David|||Thanks, David. You definitely got the ball rolling. After being able to
execute the query with the Oracle Provider for OLE DB I was still only
receiving a subset of the total rows in most of the tables. Then the
breakthrough, after changing all the server options to "True" in the link
server properties everything work perfectly.
Thanks, Thandi
"David Browne" wrote:
> "Problems w/ SQL2K5 Oracle Linked Server"
> <ProblemswSQL2K5OracleLinkedServer@.discussions.microsoft.com> wrote in
> message news:1E41CFD4-9A3E-461F-8B1D-2E3D7BF3F711@.microsoft.com...
> > Thanks, but removing the semi-colon eliminated the error but still did not
> > return the full result set. Still need help in getting all of the rows
> > back
> > regardless of the provider.
> >
> What version of the Oracle client and what version of Oracle?
> David
>
>sql