Avatar of paulrausch
paulrausch
Flag 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.
Microsoft SQL Server 2005Crystal Reports

Avatar of undefined
Last Comment
James0628

8/22/2022 - Mon
peter57r

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

This seems to work fine for me.
paulrausch

ASKER
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.
Norman Maina

Install the sql server native client tools.
Look for it here:(sqlncli.msi)
http://www.microsoft.com/en-us/download/details.aspx?id=15748
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
paulrausch

ASKER
Already have the native tools installed.

'Installation of Microsoft SQL Server Native Client failed because a higher version already exists on the machine'
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.

mlmcc
Norman Maina

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
paulrausch

ASKER
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.
paulrausch

ASKER
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.
ASKER CERTIFIED SOLUTION
Norman Maina

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
paulrausch

ASKER
Field Explorer is currently grey'd out in Crystal. Im assuming from no connection to the SQL Server.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Norman Maina

On the Database Menu,when you click on Verify Database -what do you get?
vasto

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 McCracken

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
James0628

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