Solved

Crystal  & SQLSERVER RUNTIME ERROR

Posted on 2000-03-24
5
4,506 Views
Last Modified: 2008-02-20
Running Crystal against SQLSERVER DB I get  Runtime Error 20599  Unable to open SQLserver. has anyone seen this error, what is it a symptom of and what is the resolution??
0
Comment
Question by:nbaker
  • 4
5 Comments
 
LVL 1

Accepted Solution

by:
sramkris earned 50 total points
ID: 2655611
The following issue refers to using the active data driver with the print engine Crpe32.dll This includes using the Crystl32.ocx, cpeaut32.dll, and API calls through the crpe32.dll.

Test 1:
The Report was created off an ADO recordset object to a Microsoft (MS) SQL Server database:
   
At runtime, a valid recordset is passed to the report using the SetPrivateData method of the DatabaseTable Object.

The following errors were received:

Seagate Crystal Reports:

Database Error "Microsoft OLE DB Provider for ODBC Drivers "
"[Microsoft][ODBC Driver Manager] Login Failed for user admin"

Microsoft Visual Basic:

"Runtime error 20599 - Can't Open SQL Server"

Test 2:
The Report was created off an ADO recordset to an unsecured access database. The DSN was then removed:
   
At runtime, a valid recordset is passed to the report using the SetPrivateData method of the DatabaseTable Object.

The following errors were received:

Seagate Crystal Reports:

Database Error
"Microsoft OLE DB Provider for ODBC Drivers " "[Microsoft][ODBC Driver Manager] Database Name Not Found and no default driver specified"

Microsoft Visual Basic:

"Runtime error 20599 - Can't Open SQL Server"

Test 3:
The Report was created off DAO recordset and the MS Access database was moved.
   
At runtime, a valid recordset was passed to the report using the SetPrivateData method of the Table Object.

The following errors were received:

Seagate Crystal Reports:

Database Error
"DAO Workspace" "Couldn't find file 'c:\Databases\xtreme.mdb'"

Microsoft Visual Basic:

"Runtime error 20599 - Can't Open SQL Server"


Solution

This issue occurs on all Active Data reports which have been created using the Active Data driver, P2smon.dll version 60046 or higher and the "Data Source" option chosen was either ADO, RDO or DAO. Reports which are created with the Data Definition file (TTX file), as the data source, do not encounter this issue.

There are two workarounds available as a solution. One is to create the report with a Data Definition file (TTX) and the other workaround is to use the Report Design Component's (RDC) Automation Server

To create the report using a Data Definition File, see 'Creating Field Definition Files' in the developers help for more information on how this is done.

The next solution is to use the Report Design Component's (RDC) Automation Server. This Automation Server does not use the Crpe32.dll, instead using it's own print engine (Craxdrt.dll).

0
 
LVL 1

Expert Comment

by:sramkris
ID: 2655613
Create an application in Microsoft Visual Basic using the Crystal Report Control (Crystl32.ocx). In the application change the data source for the report to point to a different Server and Stored Procedure. The following error is received when the application is run:

General SQL server error: error detected by database error. 20599


Solution

The following steps illustrate how to successfully change the Server and Stored Procedure:

In the Crystal Report Designer:
1. Open the report.
2. On the 'Database' menu click 'Set Location'
3. In the 'Table' box remove the Database and Owner. For example:
'pubs.dbo.Proc(byroyalty;1)' becomes 'Proc(byroyalty;1)'
4. Click 'OK' and save the report

In Microsoft Visual Basic:
1. Open the application
2. Enter the following code to change the Server and Store Procedure

'Connect to the new server
CrystalReport1.Connect = "DSN=NewServername;UID=username;PWD=password;DSQ=databasename"

'Change the Stored Procedure
CrystalReport1.Datafiles(0) = " Proc(Newbyroyalty;1)"


0
 
LVL 1

Expert Comment

by:sramkris
ID: 2655614
The following applies to both the 'Crystal Report Designer component Automation server' (Craxdrt.dll) and the 'Crystal Report Engine Automation server' (Cpeaut32.dll).

Create a report using a Trusted Connection to Microsoft SQL server. The report can be created as a standard report (RPT) in the Crystal report Designer or as an ActiveX Designer (DSR) in the Report Designer Component. Run the report through a Microsoft Visual Basic (VB) application. The following error is produced:

"Error 20599: Cannot Find SQL Server"


Solution

In order to use the Trusted Connection at runtime, the report has to be created using OLE DB to connect to Microsoft SQL server. Then in VB pass "<<Use Integrated Security>>" as the User ID parameter in the SetLogOnInfo property of the DatabaseTable object. For Example:

Crystal Report Designer component Automation server code.

Option Explicit
Dim crxApp As New Craxdrt.Application
Dim crxRpt As Craxdrt.Report

Private Sub Form_Load()
'Open the report
Set crxRpt = crpApp.OpenReport("C:\Training\SQLOLEDB.rpt")

'Connect to the database using integrated security
crxRpt.Database.Tables(1).SetLogOnInfo "", "", "<<Use Integrated Security>>", ""

'Preview the report
crxRpt.Preview
End Sub


Crystal Report Engine Automation server code.

Option Explicit
Dim crpApp As New CRPEAuto.Application
Dim crpRpt As CRPEAuto.Report

Private Sub Form_Load()
'Open the report
Set crpRpt = crpApp.OpenReport("C:\Training\SQLOLEDB.rpt")

'Connect to the database using integrated security
crpRpt.Database.Tables(1).SetLogOnInfo "", "", "<<Use Integrated Security>>", ""

'Preview the report
crpRpt.Preview
End Sub

0
 
LVL 1

Expert Comment

by:sramkris
ID: 2655615
Passing a GroupSelectionFormula from Microsoft Visual Basic to a report results in an error:

20599: Cannot find SQL Server

Running the same Group Selection Formula from the report prompts for logon to the database and after the report is logged on, it brings back the expected results


Solution

Use the ServerName from the report 'Database|Set Location' menu and pass this using the CrystalReport1. Connect string from VB to the report.
eg.:
CrystalReport1.Connect = "DSN=ServerName;UID=Userid;PWD=Password;DSQ="

Running the report now works.

When passing the GroupSelectionFormula to the report, the report is forced to connect to the database and not report off data that was saved with the report. Passing the Connect property over allows the report to connect to the database and process the requested selection criteria.

Use the ServerName from the Seagate Crystal Report 'Database|Set Location' menu and pass this using the CrystalReport1. Connect string from VB to the report.

For example:

CrystalReport1.Connect = "DSN=ServerName;UID=Userid;PWD=Password;DSQ="

When passing the GroupSelectionFormula to the report, the report is forced to connect to the database and not report off data that was saved with the report. Passing the Connect property over allows the report to connect to the database and process the requested selection criteria.

NOTE: Error 20599: Cannot Load SQL Server, is the same error as 20536: Unable to Connect; Incorrect Logon Parameters which appeared in previous versions of Crystal Reports.


0
 

Author Comment

by:nbaker
ID: 2669490
His Friday, March 24 2000 - 03:50PM PST
comment is exactly what is need to fix the problem.
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

785 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