Connecting Crystal Report to SQL Server

[Ishamel]

I have an application that uses a number of Crystal Reports to connect against a choice of SQL Servers.

At runtime the Crystal Reports should connect to the same Server/Database that the application is connected to, however I am having trouble producing a report if the Server I try to connect to, is not the Server that I used when designing the Crystal Report.

ie. If I design a report using Server A and try to run the report when connected to Server A, everything works fine.

However, if I try and run the same report when connected to Server B, I get the message 'SQL Server error'.

(The report I am using is nothing complex and the Databases on each Server are identical in design.)

The only thing I am trying to change is the connect property.

rptReport.Connect = "DSN=myDSN;UID=myUID;PWD=myPWD;Database=myDatabase"


If this makes sense, can anyone tell me what I am doing wrong??

Any assistance would be grately appreciated - I've been stewing on this one for about 5 months now. :(

Thanks

[Serge]

When you're in Crystal Reports designer, select File and make sure that the Save Data With Repots is unchecked.


Regards,

------------------

Serge

Software Developer
Serge_Dymkov@vertexinc.com
Access8484@aol.com
ICQ#: 51055819 (http://www.icq.com/51055819)



[This message has been edited by Serge (edited 11-09-1999).]

[baroberts]

I do the same thing with Crystal and SQL Server. But I have no problem. Perhaps the difference is that:
- in the ODBC setup I have the DSN defined for a specific server, userid, and pwd. If I need to change servers I modify the DSN to point to the other server.
- I am not using VB to execute the report. It is included in a Web page on an NT server with Crystal's web product. The server product registers itself as the application to use for "RPT" files and goes from their.

Maybe if you let the DSN supply the ODBC server, name and password it might work...ie, leave them out of the connection string.

[Pratyush]

The Problem is that you have created the report by using the SQL server directly.
You should write the report using the DSN.
When you create a Report it contains the SQL Server name before the table name like :-
SQLServer.dbo.employeemast
You can verify using change database option of Crystal Report.So when you change the database in DSN it still searches for the SQL Server reference which got used while report writing.

So you have to just convert your reports from SQL Server to DSN-based reports

[Ishamel]

Thanks for your replies guys.

I don't think I was being clear enough.

I can swap between Servers without any problems so long as the DSN is the same DSN that the report was created with.

The problem occurs when I try to run the report against server with a DSN that the report was not created with.

Any comments would be much appreciated.