|
|
 |
 |
 |
 |
Crystal Reports - Oracle Issue
Guys, just wondering what your input would be on this.
I have Crystal 9 reports which connect to an Oracle 9i database. They
do this through the ODBC driver provided by Oracle (9.2.0.7.0). The
problem occurs when I generate multiple reports. The first two
reports always work, but the third one always results in a Crystal
object in my code throwing an "incorrect logon parameters" exception.
To me this feels a lot like the DB connections that are being opened
aren't closed until my entire app is shut down. Is it normal for an
Oracle DB to allow two connections from the same IP, but then reject a
third attempt? Perhaps because it exceeds a threshold of two set by
some administrator?
Thanks.
On May 10, 2:54 pm, s_valm @telus.net wrote: > Guys, just wondering what your input would be on this. > I have Crystal 9 reports which connect to an Oracle 9i database. They
> do this through the ODBC driver provided by Oracle (9.2.0.7.0). The
> problem occurs when I generate multiple reports. The first two
> reports always work, but the third one always results in a Crystal
> object in my code throwing an "incorrect logon parameters" exception.
> To me this feels a lot like the DB connections that are being opened
> aren't closed until my entire app is shut down. Is it normal for an
> Oracle DB to allow two connections from the same IP, but then reject a
> third attempt? Perhaps because it exceeds a threshold of two set by
> some administrator?
> Thanks.
It appears to be more of a memory issue in my estimation. How is the machine running Crystal Reports configured? David Fitzjarrell
-----------------------------------------------Reply-----------------------------------------------
On May 10, 3:54 pm, s_valm @telus.net wrote: > Guys, just wondering what your input would be on this. > I have Crystal 9 reports which connect to an Oracle 9i database. They
> do this through the ODBC driver provided by Oracle (9.2.0.7.0). The
> problem occurs when I generate multiple reports. The first two
> reports always work, but the third one always results in a Crystal
> object in my code throwing an "incorrect logon parameters" exception.
> To me this feels a lot like the DB connections that are being opened
> aren't closed until my entire app is shut down. Is it normal for an
> Oracle DB to allow two connections from the same IP, but then reject a
> third attempt? Perhaps because it exceeds a threshold of two set by
> some administrator?
> Thanks.
Have you tried to change the order of reports and got the same behavior? -----------------------------------------------Reply-----------------------------------------------
On May 10, 12:56 pm, "fitzjarr@cox.net" <fitzjarr@cox.net>
wrote:
> It appears to be more of a memory issue in my estimation. How is the > machine running Crystal Reports configured? > David Fitzjarrell
Why are you thinking memory, David? The machine running Crystal is a .NET 1.1 webserver. How do you mean "configured" exactly? -----------------------------------------------Reply-----------------------------------------------
On May 10, 3:49 pm, s_valm @telus.net wrote: > On May 10, 12:56 pm, "fitzjarr @cox.net" <fitzjarr @cox.net> > wrote: > > It appears to be more of a memory issue in my estimation. How is the
> > machine running Crystal Reports configured?
> > David Fitzjarrell
> Why are you thinking memory, David? The machine running Crystal is
> a .NET 1.1 webserver. How do you mean "configured" exactly?
How much RAM is installed? What else is running on the Windows server besides Crystal Reports? It appears you're losing connection data likely because you haven't sufficient memory to run all that you're asking this machine to run. David Fitzjarrell
-----------------------------------------------Reply-----------------------------------------------
On May 10, 1:14 pm, Valentin Minzatu <valentinminz@yahoo.com>
wrote:
> Have you tried to change the order of reports and got the same
> behavior?
I have about 10 reports and neither changing them nor their order makes a difference in this case, Valentin. -----------------------------------------------Reply-----------------------------------------------
On May 10, 2:05 pm, "fitzjarr@cox.net" <fitzjarr@cox.net> wrote:
> How much RAM is installed?
> What else is running on the Windows server besides Crystal Reports?
> It appears you're losing connection data likely because you haven't
> sufficient memory to run all that you're asking this machine to run.
> David Fitzjarrell
Really? I had no idea this could be a factor. The issue occurs on our production server which is very capable but
runs a truckload of web apps. However, the thing is I am also
analyzing and reproducing this problem using my local host which has 1
GB of RAM and runs nothing other than Microsoft Visual Studio 2003 and
Outlook.
Can you explain what connection data consists of? Does it usually
require a lot of memory?
-----------------------------------------------Reply-----------------------------------------------
s_valm @telus.net wrote: > Guys, just wondering what your input would be on this. > I have Crystal 9 reports which connect to an Oracle 9i database. They
> do this through the ODBC driver provided by Oracle (9.2.0.7.0). The
> problem occurs when I generate multiple reports. The first two
> reports always work, but the third one always results in a Crystal
> object in my code throwing an "incorrect logon parameters" exception.
> To me this feels a lot like the DB connections that are being opened
> aren't closed until my entire app is shut down. Is it normal for an
> Oracle DB to allow two connections from the same IP, but then reject a
> third attempt? Perhaps because it exceeds a threshold of two set by
> some administrator?
> Thanks.
It is far better to connect to Oracle using the native connectivity. Get as far away from ODBC as you can. It may not solve your problem but it will still be a good idea.
--
Daniel A. Morgan
University of Washington
damor@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
-----------------------------------------------Reply-----------------------------------------------
On May 10, 5:44 pm, s_valm @telus.net wrote: > On May 10, 1:14 pm, Valentin Minzatu <valentinminz @yahoo.com> > wrote: > > Have you tried to change the order of reports and got the same
> > behavior?
> I have about 10 reports and neither changing them nor their order
> makes a difference in this case, Valentin.
So, regardless if you run a report which *ran* first or any other, as long as it is run the 3rd it will fail? The error message you are getting suggests that somehow somewhere the
login information is changed - I assume you use the same login for all
reports. I would concentrate on that area first and eliminate all
possible causes (i.e. files, data sources, etc.). I doubt this has to
do with memory or anything along those lines, but it is not completely
impossible to be hitting a bug in the ODBC driver - still highly
improbable in my opinion.
-----------------------------------------------Reply-----------------------------------------------
On May 10, 3:21 pm, DA Morgan <damor @psoug.org> wrote: > It is far better to connect to Oracle using the native connectivity.
> Get as far away from ODBC as you can.
> It may not solve your problem but it will still be a good idea.
What exactly might be a problem with ODBC? I ask because a customer appears to be moving towards sucking out a lot of stuff into MS analytics cubes and such. I think it's using ODBC, but not sure how to tell. This is in the listener.log (all that's left after removing site-specific identifiers): CID=(PROGRAM=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
\sqlservr.exe)
At one point, I had shown the customer how to get this stuff (or
something like it) going with instant client as the driver, but I
don't know what is happening now (a vendor has recently done an
install of a beta product, I'm not involved on that end). I don't
know how different this is than a typical Crystal suck or whatever, it
does appear to be hitting the db with some frequency at semi-random
times. Haven't had a chance to see exactly what it is doing yet,
assume it is just grabbing rows from a few key tables.
So I'm interested in what might be wrong with ODBC from the server
point of view. If it blows on the other end, that doesn't bother me
so much. They're talking about maybe turning this into a full-blown
DSS, which would be fine by me, since that would mean plenty of work
on the OLTP side (translation: Oracle db upgrade and 3rd party app
upgrade with perhaps a de-customization, haha), and less worry about
DSS queries from the clueless on the production system. And maybe I'd
learn something about the dark side, too.
jg
--
@home.com is bogus.
Web 2.0 drinking game: http://richarddcrowley.org/blog/view/131
-----------------------------------------------Reply-----------------------------------------------
On 11 May 2007 17:11:39 -0700, joel garry <joel-ga @home.com> wrote:
>On May 10, 3:21 pm, DA Morgan <damor @psoug.org> wrote: >> It is far better to connect to Oracle using the native connectivity.
>> Get as far away from ODBC as you can.
>> It may not solve your problem but it will still be a good idea.
>What exactly might be a problem with ODBC? I ask because a customer
>appears to be moving towards sucking out a lot of stuff into MS
>analytics cubes and such. I think it's using ODBC, but not sure how
>to tell. This is in the listener.log (all that's left after removing
>site-specific identifiers):
>CID=(PROGRAM=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
>\sqlservr.exe)
>At one point, I had shown the customer how to get this stuff (or
>something like it) going with instant client as the driver, but I
>don't know what is happening now (a vendor has recently done an
>install of a beta product, I'm not involved on that end). I don't
>know how different this is than a typical Crystal suck or whatever, it
>does appear to be hitting the db with some frequency at semi-random
>times. Haven't had a chance to see exactly what it is doing yet,
>assume it is just grabbing rows from a few key tables.
>So I'm interested in what might be wrong with ODBC from the server
>point of view. If it blows on the other end, that doesn't bother me
>so much. They're talking about maybe turning this into a full-blown
>DSS, which would be fine by me, since that would mean plenty of work
>on the OLTP side (translation: Oracle db upgrade and 3rd party app
>upgrade with perhaps a de-customization, haha), and less worry about
>DSS queries from the clueless on the production system. And maybe I'd
>learn something about the dark side, too.
>jg
If you use a function in your where clause, which can't be mapped by the Evil Empire to an Oracle function, the Evil Empire will cause all data to be fetched to the client, and have it filtered on the client. Also: actual execution paths are often unpredictable, as ODBC may
decide to split a join up in two different queries, one being executed
on the server, and one executed on the client, and the join being
executed on the client.
The probability of this will increase if you combine Oracle and
non-Oracle tables.
Long ago, I ended up with converting most queries into passthrough
queries, because otherwise the performance was just horrible due to
the reasons above.
Mind you: as far as I remember Billy-boy, Stevie-boy and his
associates couldn't even map Microsux Jaccess date functions to
to_char and to_date.
If you don't have heterogeneous sources, don't use ODBC, if your app
supports a native Oracle driver.
--
Sybrand Bakker
Senior Oracle DBA
-----------------------------------------------Reply-----------------------------------------------
On May 11, 8:11 pm, joel garry <joel-ga @home.com> wrote:
> On May 10, 3:21 pm, DA Morgan <damor @psoug.org> wrote: > > It is far better to connect to Oracle using the native connectivity.
> > Get as far away from ODBC as you can.
> > It may not solve your problem but it will still be a good idea.
> What exactly might be a problem with ODBC? I ask because a customer
> appears to be moving towards sucking out a lot of stuff into MS
> analytics cubes and such. I think it's using ODBC, but not sure how
> to tell. This is in the listener.log (all that's left after removing
> site-specific identifiers):
> CID=(PROGRAM=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
> \sqlservr.exe)
> At one point, I had shown the customer how to get this stuff (or
> something like it) going with instant client as the driver, but I
> don't know what is happening now (a vendor has recently done an
> install of a beta product, I'm not involved on that end). I don't
> know how different this is than a typical Crystal suck or whatever, it
> does appear to be hitting the db with some frequency at semi-random
> times. Haven't had a chance to see exactly what it is doing yet,
> assume it is just grabbing rows from a few key tables.
> So I'm interested in what might be wrong with ODBC from the server
> point of view. If it blows on the other end, that doesn't bother me
> so much. They're talking about maybe turning this into a full-blown
> DSS, which would be fine by me, since that would mean plenty of work
> on the OLTP side (translation: Oracle db upgrade and 3rd party app
> upgrade with perhaps a de-customization, haha), and less worry about
> DSS queries from the clueless on the production system. And maybe I'd
> learn something about the dark side, too.
> jg
> --
> @home.com is bogus.
> Web 2.0 drinking game:http://richarddcrowley.org/blog/view/131
It truncates values at 255 and does not handle binary objects well, but there are ways around this: http://www.ffnn.nl/pages/articles/mono-and-c/large-binary-fields-usin...
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc