Server Not Yet Opened Error & Unable to Log off

Posted on 2004-08-19
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
  • 3
  • 2
  • 2
  • +2

Expert Comment

ID: 11839483
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:


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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

LVL 100

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 250 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 250 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: (…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

806 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