Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Server Not Yet Opened Error & Unable to Log off

Posted on 2004-08-19
Medium Priority
Last Modified: 2008-01-09
Hi gurus,

Can you give me a hand?
I'm using VB6, CR8.5 and Oracle 8.1.7
I'm using ADO to call the stored proc from VB.
And RDC to invoke the Crystal Reports.
The reports datasource is defined via ODBC.

I invoke a set of reports, and have noticed that my DB connections are not closed.
In the end, this reaches the maximum number of session allowed on the DB and hence leads to the Server Not Yet Opened Error.
However, if I try to use the LogOffServer method - I get a Unable to log off error!!!
So, how do I close my DB connection?

Here is some sample of the ReportToFile function that I call multiple times:
    Set craxApp = New CRAXDRT.Application
    Set craxReport = New CRAXDRT.Report
    Set craxReport = craxApp.OpenReport(p_sReportPath & sReportName)
    craxReport.ExportOptions.DiskFileName = p_sDestinationPath & sDestinationFileName & p_sFileExtension
    craxReport.ExportOptions.DestinationType = crEDTDiskFile
    craxReport.ExportOptions.FormatType = crEFTExcel80
    craxReport.Database.LogOnServer "p2sodbc.dll", "MyDB", , GL_ORACLE_UserID, GL_ORACLE_Password
    craxReport.DisplayProgressDialog = False
    craxReport.Export False
    Set craxApp = Nothing
    Set craxReport = Nothing
    Exit Sub

Thanks in advance for your assistance.

Question by:Danny_Duong
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 42

Expert Comment

ID: 11840029

It could be related to the second link posted by Z03niE - but probably not the first since you said you're using RDC.

It could also be an Oracle configuration issue - see this document for the details about connection pooling that can cause this problem: http://support.businessobjects.com/library/kbase/articles/c2008265.asp


LVL 13

Expert Comment

ID: 11840863
Using the SetLogOnInfo method instead of LogOnServer will take care of closing the connection:

craxRpt.Database.Tables(1).SetLogOnInfo DSN_NAME, DB_NAME, USER_ID, USER_PASS

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 101

Expert Comment

ID: 11840940
As I recall CR8.5 logs off but the connection can remain active for 20-30 minutes even though you are logged off.

DO you possibly have the VB connection to the database still open?


Author Comment

ID: 11847156
Thanks guys - I've read most of these doco before - they don't seem to help.
It all point to using SetLogOnInfo rather than LogOnServer.
However, I'm not sure how to use the SetLogOnInfo.
Do I need to specify every table that my report & subreport access?
Can you give me an example as per my code sniplet above?


Author Comment

ID: 11848421
I've got it working now. I've changed one line of code.
From: craxReport.Database.LogOnServer "p2sodbc.dll", "MyDB", , GL_ORACLE_UserID, GL_ORACLE_Password
To: craxReport.Database.Tables(1).SetLogonInfo "MyDB", , GL_ORACLE_UserID, GL_ORACLE_Password

I don't really know what I'm doing - but it worked!!!

What is the difference between these two lines? They both seem to work for me, but I'm not too sure which one to use, i.e. for complex reports with sub reports where it references more than 1 table - which of the following is better to use?
craxReport.Database.Tables(1).SetLogonInfo "MyDB", , GL_ORACLE_UserID, GL_ORACLE_Password
craxReport.Database.Tables.Item(1).SetLogonInfo "MyDB", , GL_ORACLE_UserID, GL_ORACLE_Password


LVL 13

Accepted Solution

vidru earned 1000 total points
ID: 11848566
This document has some great information on db connectivity, and gives good explanations of both the LogOnServer and SetLogOnInfo methods:

In most of my apps, just to be on the safe side, I set the Logon info for all of the tables:

Dim crxDBTables As CRAXDRT.DatabaseTables
Dim crxDBTable As CRAXDRT.DatabaseTable

Set crxDBTables = crxRpt.Database.Tables

For Each crxDBTable In crxDBTables
   crxDBTable.SetLogOnInfo "Server", "DB", "UserID", "Password"

Then, you can loop through the report's sections to find the subreports, and do the same thing for them.


Author Comment

ID: 11866291
I've just used craxReport.Database.Tables(1).SetLogonInfo "MyDB", , GL_ORACLE_UserID, GL_ORACLE_Password
All my reports seem to work fine.

Just wonderring why you would have to set this Login Info for each table the report & subreport?
LVL 42

Assisted Solution

frodoman earned 1000 total points
ID: 11870558
>>> Just wonderring why you would have to set this Login Info for each table the report & subreport?

From a security standpoint, you may need different login for each table - perhaps one userid can view the first table but not the second.  Certainly this isn't the most common scenario but with this approach the door is left open to do that if needed.  

As far as subreports it's really the same reason.  I have a case where the main report tables login with one id and the subreport with another id -- this is so managers see their own data in the main report and see data from their subordinates perspective as the subreport level.  Again this isn't a common approach but sometimes is necessary.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question