What are some of the conditions that can cause the "Cannot open SQL Server" error message?

Frequently Asked Questions

What are some of the conditions that can cause the “Cannot open SQL Server” error message?

Download PDF version of this faq: CannotOpenSqlServer.pdf
FAQ# 26-04250203

This error message is usually caused when the application tries to read the data from the database without first providing database logon information. Another possibility is that database logon information has been provided, but the information is invalid or has changed.

Issue No 1
"Cannot open SQL Server" and report generates with no data in it:

The Microsoft SQL Server native database driver that Seagate/Crystal Decisions ships with Crystal Reports 7.0.100.6 is defective. Parallel Crystal v2.3, v2.4, v2.5, v2.6 and v2.7 are based on Crystal 7 and have this same driver as part of their installation.

If a report uses a native connection (P2ssql.dll) to a Microsoft SQL Server stored procedure, when a Date Time type parameter is passed to the stored procedure in the Seagate Crystal Reports Designer, the report runs returning correct data.

However, when the report is called at runtime with the same parameter value, no data is returned. The report does not return any error messages and appears as a report with no data in it.

Solution
This is a known issue with the P2ssql.dll (version 7.0.100.6) that comes with version 7 Maintenance Release 1 of Seagate Crystal Reports.

To resolve this issue:
Download: P2ssql.dll
See also: http://support.crystaldecisions.net/library/kbase/articles/c2003968.asp

Note: According to the Crystal Decisions web site, there is a later Microsoft SQL Server direct database driver. Please do not install or use the later driver with Dynalivery Parallel Crystal v2.3, v2.4, v2.5, v2.6 and v2.7.

Also make sure you are using p2s*. dlls which are the newer 32bit DLLs rather than pds*.dlls which are 16bit DLLs.p2s*.dlls can be used directly(not getting it from table type)with LogOnServer which takes a DLL name and a LogOnInfo structure.

Issue No 2
"Cannot open SQL Server" When running reports that use a native connection to a database.

Solution
Make sure that, if your report is accessing a database and is not saved with data, your application is logging onto the database. One of the reasons in which this error is thrown is when the logon information is not present in your application code. Please try the sample connection code below.

Sample code for Microsoft SQL Server database log on
Java Code Sample
ASP Code Sample

// JAVA log into the SQL Server through the native SQL driver
PCRELogOnInfo logOnInfo = new PCRELogOnInfo();
logOnInfo.ServerName = "mssqltest";
logOnInfo.DatabaseName = "Northwind";
logOnInfo.UserID = "dummyuser";
logOnInfo.Password = password;

/* Note: Use the appropriate dll in LogOnServer() API for the DBMS used */

boolean logOnStatus = engine.LogOnServer ( "P2SSQL.DLL", logOnInfo );
if ( logOnStatus )
System.out.println (“Log on succeeded”);
else
System.out.println (“Log on failed”);

ASP Sample Code

' Log on to the SQL Server through the native SQL driver
set logOnInfo = Server.CreateObject("PCRE.PCRELogOnInfo")
logOnInfo.ServerName = "MSSQLTEST"
logOnInfo.DatabaseName = "Northwind"
logOnInfo.UserID = "dummyuser"
logOnInfo.Password = "password"

' Note: Use the appropriate dll in LogOnServer() API for the DBMS used
logOnStatus = objEngine.LogOnServer ("p2ssql.dll", logOnInfo)
If (logOnStatus)Then
Response.write("Log on succeeded")
Else
Response.write("Log on failed")
End If

Another possible reason is that the client to your DBMS is not installed on the report server. To fix this simply run the install shield for your DBMS and install the “client only” on the report server.

Issue No 3
When doing a native connection you can use either the Engine level LogOn or the job level LogOn. Please refer to the "Database Logon: Native, ODBC and DOR" FAQ.

The log on portion of your code should look like the log on portion of the sample code available from this link: http://www.dynalivery.com/customersupport/faq/databaselogon.html

Issue No 4 (Access of Sybase 11 or successors database with old database driver)

Parallel Crystal v2.4, 2.5, 2.6, and v2.7 are based on Crystal Reports 7. The latest Sybase driver that was available at the time CR v7 was released (1999) is the Sybase version 10 driver. Access of Sybase 11 or successors with the old (version 10) driver is not stable and has some known problems.

The Sybase 32bit native driver provided by Crystal Decisions is called “p2ssyb10.dll”

They Sybase System 10 ODBC driver provided by Crystal Decisions is called “crsyb13.dll”

There is a subtle incompatibility between the Sybase 10 database driver p2ssyb10.dll, and the Sybase 12 DBMS that may "freeze" the first data source specified in a report template. Sybase 11+ users may not be able to use one report template for different data sources.

If the database logon/logoff parameters are different at run time then it will be necessary to consider alternatives. Dynalivery recommends that customers in this situation use our preferred method for data acquisition, Data Object Reporting (DOR).

In the event that it is not possible or desirable to use DOR it will be necessary to create a separate template for each data source.

ODBC may be a third alternative but we highly recommend thoroughly testing this solution first. Lastly you may want to consider upgrading to Parallel Crystal v3.1 (Crystal Reports 8.5) or successors.

PCRE errors that may be associated with this problem are:

Internal Error: code 599 PEStartPrintJob returned false
Error text: Cannot open SQL server

Conditions under which this condition may arise:

Using Crystal 7 with PCRE 2.4, 2.5, 2.6, and 2.7 with Sybase 11+
When a single template that works in development is promoted to production and the data source is changed at run time.

Issue No 5
If you are connecting to an Oracle database, the database parameter must be left blank i.e., you need to pass nothing. Passing an empty string is not the same and will cause problems. Also if you are connecting natively, the server name is the TNSNAMES alias, not the server computer's name. For ODBC it is always the DSN name.

Issue No 6
There are some known issues when using Oracle native drivers. If you are using Oracle then we would recommend using an ODBC connection or our Data Object Reporting (DOR) feature.

There is additional information on these error messages in the Crystal Reports 6 and 7 Developer's Help Systems and Technical Reference Manuals.