Delphi 5, Crystal Reports + SQL Server

[StevenK]

I have a Delphi 5 application working against an SQL Server back-end. The user is able to select to connect to one of a number of databases on the server (databases have identical structures).
I am now trying to dynamically set the source for the Crystal Report through code. The designed report works successfully using the SQL Server designed on but when I try to connect to a different server I have problems.
I am using syntax as follows :
CRReport := Application1.OpenReport(tpRptPath, 1);
Application1.LogOnServer('p2sodbc.dll', 'SERVER-NAME', 'DATABASE-NAME', 'user-name', 'password');
This generates the error message "Server has not yet been opened."
What instruction do I need to call first ?

I have tried an alternate approach by dynamically setting the SQL query string for the report using code :
CRReport.Set_SQLQueryString('SELECT ...
FROM [SERVER-NAME].[DATABASE-NAME].dbo.TABLE-NAME
WHERE ....');
On running this I am given the errors :
"General SQL Server Error. Check messages from the SQL Server."
"Error detected by database DLL."
What am I doing wrong ?

 

[dcm]

StevenK: I think this is an oder of execution issue - try placing the LogonServer before OpenReport - the logged on server connections are idependent of the report BUT the report does expect the connection to be there in order to operate David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[StevenK]

dcm : Thanks for the suggestion. I am now running the code in the order :
Application1.LogOnServer('p2ssql.dll', [SERVER-NAME], [DATABASE-NAME], 'sa, '');
CRReport := Application1.OpenReport(tpRptPath, 1);
On trying to set ythe SQL through the command CRReport.Set_SQLQueryString(...) I am still presented with the error message "Server has not yet been opened."
I do not get any errors if the Crystal Report was generated using a connection to the same server (as application is to point to). The error occurs when trying to access data on a server other than the one against which the report was originally designed.
The report object is obviously maintaining a connection to the server against which the report was created - how do I break this association to connect it successfully ?
Re-creating the same report against the appropriate SQL Server allows the code to run successfully.
Can anyone make any suggestions as to what else I should be doing ?

 

[BrendaLeigh]

With Crystal Reports 6 and higher, it is possible to change the Database, Table, or directory that an ODBC Datasource points to without having to reconfigure the ODBC Datasource manually. The change is done via the DatabaseName property of the LogOnInfo or Connect objects. The syntax required to make the change is in the following format:

<CRWDC>[Database Spec]=

The syntax is slightly different depending on which ODBC database driver the Report is using. Here is a categorical list showing the appropriate syntax in each case:

SQL ODBC drivers


Crpe1.Connect.DatabaseName := '<CRWDC>Database=' + DatabaseName;

PC ODBC drivers

Access
Crpe1.Connect.DatabaseName := '<CRWDC>DBQ=' + Path\DatabaseName;

DBase
Crpe1.Connect.DatabaseName := '<CRWDC>DBQ=' + Path\;