Server Not Yet Opened Error & Unable to Log off

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.DiscardSavedData
    craxReport.Database.LogOnServer "p2sodbc.dll", "MyDB", , GL_ORACLE_UserID, GL_ORACLE_Password
...
    craxReport.DisplayProgressDialog = False
    craxReport.Export False
    DoEvents
...
Exit_Routine:      
    Set craxApp = Nothing
    Set craxReport = Nothing
    Exit Sub

Thanks in advance for your assistance.

Danny.
Danny_DuongAsked:
Who is Participating?
 
vidruConnect With a Mentor Commented:
This document has some great information on db connectivity, and gives good explanations of both the LogOnServer and SetLogOnInfo methods:
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_rdc_dbconnectivity.pdf.asp

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"
Next

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

-dave
0
 
frodomanCommented:
Danny,

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

HTH

frodoman
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
vidruCommented:
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

-dave
0
 
mlmccCommented:
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?

mlmcc
0
 
Danny_DuongAuthor Commented:
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?

Thanks.
Danny.
0
 
Danny_DuongAuthor Commented:
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

Cheers.
Danny.

0
 
Danny_DuongAuthor Commented:
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?
0
 
frodomanConnect With a Mentor Commented:
>>> 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.

frodoman
0
All Courses

From novice to tech pro — start learning today.