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
Showing posts with label experiencing. Show all posts
Showing posts with label experiencing. Show all posts
Wednesday, March 28, 2012
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(*) fro
m
> 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.Orac
le"
> 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 Linke
d
> Server@.discussions.microsoft.com> wrote in message
> news:0F1603EE-0DEE-47B4-8F8A-40F16BE0FA6E@.microsoft.com...
> 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 terminiato
r
> in PL/SQL, but is forbidden in Oracle SQL.
> David
>
>|||"Problems w/ SQL2K5 Oracle Linked Server"
< ProblemswSQL2K5OracleLinkedServer@.discus
sions.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@.discus
sions.microsoft.com> wrote in
> message news:1E41CFD4-9A3E-461F-8B1D-2E3D7BF3F711@.microsoft.com...
> What version of the Oracle client and what version of Oracle?
> David
>
>
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(*) fro
m
> 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.Orac
le"
> 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 Linke
d
> Server@.discussions.microsoft.com> wrote in message
> news:0F1603EE-0DEE-47B4-8F8A-40F16BE0FA6E@.microsoft.com...
> 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 terminiato
r
> in PL/SQL, but is forbidden in Oracle SQL.
> David
>
>|||"Problems w/ SQL2K5 Oracle Linked Server"
< ProblemswSQL2K5OracleLinkedServer@.discus
sions.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@.discus
sions.microsoft.com> wrote in
> message news:1E41CFD4-9A3E-461F-8B1D-2E3D7BF3F711@.microsoft.com...
> What version of the Oracle client and what version of Oracle?
> David
>
>
Friday, March 9, 2012
Problems with DTS
Hi there,
Suddenly, I am experiencing problems with DTS. When I right-click Local
Packages (in Data Transformation Services) an empty window appear. I can't
see the menus and toolbars.
If I choose Customize view... in the new DTS Package the following options
are disabled:
- Console tree
- Standard menus (Action and View)
- Standard toolbar
Anyone knows why? It worked just fine two weeks ago.Hello, Stefan!
It does sound as though something trod on the DTS dlls, MDAC
Have a look at reregistering then DTS dlls and maybe reinstalling MDAC.
Redistributing DTS with your program
http://www.sqldts.com/default.aspx?6,105,225,0,1
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Suddenly, I am experiencing problems with DTS. When I right-click Local
: Packages (in Data Transformation Services) an empty window appear. I can't
: see the menus and toolbars.
: If I choose Customize view... in the new DTS Package the following options
: are disabled:
: - Console tree
: - Standard menus (Action and View)
: - Standard toolbar
-- Microsoft Outlook Express 6.00.2800.1158
Suddenly, I am experiencing problems with DTS. When I right-click Local
Packages (in Data Transformation Services) an empty window appear. I can't
see the menus and toolbars.
If I choose Customize view... in the new DTS Package the following options
are disabled:
- Console tree
- Standard menus (Action and View)
- Standard toolbar
Anyone knows why? It worked just fine two weeks ago.Hello, Stefan!
It does sound as though something trod on the DTS dlls, MDAC
Have a look at reregistering then DTS dlls and maybe reinstalling MDAC.
Redistributing DTS with your program
http://www.sqldts.com/default.aspx?6,105,225,0,1
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Suddenly, I am experiencing problems with DTS. When I right-click Local
: Packages (in Data Transformation Services) an empty window appear. I can't
: see the menus and toolbars.
: If I choose Customize view... in the new DTS Package the following options
: are disabled:
: - Console tree
: - Standard menus (Action and View)
: - Standard toolbar
-- Microsoft Outlook Express 6.00.2800.1158
Monday, February 20, 2012
Problems when processing cube partitions
Hello all
I am experiencing problems with one of my cubes. We have a historical cube which is partitiones in quarters from 2004-2006, and then monthly for 2007. I have processed the dimensions in full, and now try to process only the newest cube partition, the one for june 2007, and after thats done the cube reults are quite bad, my measures are way way way off... What am I missing in here?
After processing a dimension in full mode all fact data in the cube is deleted! So now, you only have the data for June 2007.
Hope this clarifies you question.
|||Thanks for your reply, this answers my question... I wonder what to do then, as the process update takes almost double time as the process fullAre you actually updating attributes of existing members or only adding new members?
If you are only adding new ones you could look into doing a processAdd.
|||Unfortunately every friday the dimension is "cleaned" which means removing some data from it
Labels:
2004-2006,
alli,
cube,
cubes,
database,
experiencing,
historical,
microsoft,
monthly,
mysql,
oracle,
partitiones,
partitions,
processing,
quarters,
server,
sql
Subscribe to:
Posts (Atom)