Run Time Error 20599: cannot open SQL server

FAC
FAC used Ask the Experts™
on
With VB6, how to connect a SQL server database using MSDAO to crystal reports 7.0
The string connection to the sql database northwind is:

Provider=SQLOLEDB.1;User ID=sa;Initial Catalog= northwind;Data Source=servidor;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=FAC;Use Encryption for Data=False;Tag with column collation when possible=False

I use the crystal report control. Of course, I have no problem, if the report is compiled in the server where the program will run. If not, occurs then following error:
Run Time Error 20599: cannot open SQL server


Thanks for any help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
FAC

Author

Commented:
It is not MSDAO, is MSADO, obviously.
Software Development Manager
Commented:
Hi,

We had many problems with SQL Server and Crystal Reports.
It appears as though Crystal Reports actually saves the server information into the actual .RPT file.
When you distribute the report - it looks for the server information in order to print. As the server name is different it can not resolve this issue and errors occurred.

The way we worked around it at the time (we no longer use Crystal Reports), was to use an ODBC connection for Crystal Reports. The connection is called 'MyConnection' and it is created at each of our clients. It points to their local database (whatever it is called).
When the reports are designed, they are designed from the ODBC connection and not a direct SQL Server login.

We were using Crystal Reports Pro 8.0

We've since moved away from Crystal Reports and are now using ActiveReports (http://www.datadynamics.com)

HTH,
Alon

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- answered by alonhirsch

Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

ayufans
Cleanup Volunteer

Commented:
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
I faced this issue on Oracle Database environment.
Problem is on ODBC connectivity section.Please create ODBC connectivity admin,Domain admin or under relevant user permissions.
If you need further assistant please email to me.I like to help you any IT network related issues.
Dhanushka.Siriwardhana@yahoo.com

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial