Link to home
Start Free TrialLog in
Avatar of paulrausch
paulrauschFlag for United States of America

asked on

Crystal Reports - SQL Database

I have a client who used to use Deltek FMS with Crystal Reports for their Management Software. They migrated to a new system, and kept the old in the event they needed to look back at something. Their old system running Deltek FMS died, and the IT Co. before me had only done what appears to be file level backups. I now need to try and restore the database with what I have.
My Question is, I have located the .mdf and .ldf database files that I believe to be the correct ones; does anyone know how to get Crystal Reports to interface with the database? Its SQL Studio 2005 with Crystal Reports XI. I can attach the database files, but when I attempt to connect to the database via Crystal Reports I get numerous ODBC errors. I am in no way, shape, or form a SQL expert. Any help would be appreciated.
Avatar of peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

I connect using OLEDB and the SQL Server Native Client and I'm using integrated security.

This seems to work fine for me.
Avatar of paulrausch


When I go this route, I receive the following:

Logon Failed.
Details: ADO Error Code: 0x80040e73
Source: Microsoft OLE DB Service Components
Description: Format of the initialization string does not conform to the OLE DB specification.
Install the sql server native client tools.
Look for it here:(sqlncli.msi)
Already have the native tools installed.

'Installation of Microsoft SQL Server Native Client failed because a higher version already exists on the machine'
Avatar of Mike McCracken
Mike McCracken

How are you trying to connect Crystal to the database?
Are you bilding the connection in  Crystal?

Have you added/restored the database to the new SQL server?
I don't believe Crystal can connect directly to the MDF file but has to connect through the server.

After you have attached the database in sql server ,make sure the logins that were used by the previous system are the same ones you will use.
Crystal probably is using credentials used by the previous database.
Ive got a VM setup, with SQL 2005 w/ SP3 installed. Ive located 3 MDF/LDF files, and ive 'attached' them in SQL without issue.
Inside Crystal, I navigate to Files > Log on or Off Server.. and within the Data Explorer is where im receiving the errors.
After I attach the databases, I pull properties and its showing 'sa' as the owner. Is there anywhere in SQL that I can reset the password for the database 'sa' account? Upon re-installation of SQL I used mixed mode and created a new 'sa' password.
Avatar of Norman Maina
Norman Maina
Flag of Kenya image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Field Explorer is currently grey'd out in Crystal. Im assuming from no connection to the SQL Server.
On the Database Menu,when you click on Verify Database -what do you get?
There is a free tool that you can use to set the connection: I am linked to the company. Please make a copy of your report before to process it.

You can change 'sa' password if you are server administrator, but it is not a good practice to use 'sa' to open the database. Instead, create a new login and assign the necessary permissions. In SSMS - select Security ( it is the first sibling node under Databases) and create a new login. Then go to your database find the "Security" node ,create a user and link it to the new login. You will need to set the rights on page "Securables " - "Select" should be granted.
The other way is to change the report connection to use integrated security. It will be easier to check if report is working , but then you will need to manage the users on SQLserver. However , I assume you have rights to access the database and this way will work at least for you.
Are these existing reports?
If so then
Open a report
Create a NEW CONNECTION to the new database (only need be done for first report in a session)
Select the new database in the lower box
Select the database in the upper box
May have to do it by table

FOr new reports
Create the new connection
Select the tables
Create the report

To add to what everyone else has said, it might be a good idea to start by trying to create a new report and connect that to the db.  Once you get that working, you can look at trying to change the datasource for the old reports.