Crystal Reports - SQL Database

paulrausch
paulrausch used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I connect using OLEDB and the SQL Server Native Client and I'm using integrated security.

This seems to work fine for me.

Author

Commented:
Ok,
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)
http://www.microsoft.com/en-us/download/details.aspx?id=15748
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Already have the native tools installed.

'Installation of Microsoft SQL Server Native Client failed because a higher version already exists on the machine'
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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.

mlmcc
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.

Author

Commented:
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.

Author

Commented:
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.
you need to set the location of your report to the new instance by going to Field Explorer and right clicking database fields -then point it to your new instance by clicking on Set Location and selecting the data source.

Author

Commented:
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?
Top Expert 2011

Commented:
There is a free tool that you can use to set the connection: http://www.r-tag.com/Pages/CRDataSource.aspx. 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.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are these existing reports?
If so then
Open a report
Click DATABASE --> SET DATASOURCE LOCATION
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
Click UPDATE
May have to do it by table

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

mlmcc
mlmcc
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.

 James

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