[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

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.
0
Danny_Duong
Asked:
Danny_Duong
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
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
 
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
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now