Solved

Server Not Yet Opened Error & Unable to Log off

Posted on 2004-08-19
11
618 Views
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.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
Comment
Question by:Danny_Duong
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 5

Expert Comment

by:Z03niE
ID: 11839483
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11840029
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
 
LVL 13

Expert Comment

by:vidru
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

-dave
0
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

by:mlmcc
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?

mlmcc
0
 

Author Comment

by:Danny_Duong
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?

Thanks.
Danny.
0
 

Author Comment

by:Danny_Duong
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

Cheers.
Danny.

0
 
LVL 13

Accepted Solution

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

Author Comment

by:Danny_Duong
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?
0
 
LVL 42

Assisted Solution

by:frodoman
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.

frodoman
0

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: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
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