Solved

Crystal  & SQLSERVER RUNTIME ERROR

Posted on 2000-03-24
5
4,459 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
His Friday, March 24 2000 - 03:50PM PST
comment is exactly what is need to fix the problem.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now