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.
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.
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.
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
Look for it here:(sqlncli.msi)
http://www.microsoft.com/en-us/download/details.aspx?id=15748
ASKER
Already have the native tools installed.
'Installation of Microsoft SQL Server Native Client failed because a higher version already exists on the machine'
'Installation of Microsoft SQL Server Native Client failed because a higher version already exists on the machine'
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
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.
Crystal probably is using credentials used by the previous database.
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.
Inside Crystal, I navigate to Files > Log on or Off Server.. and within the Data Explorer is where im receiving the errors.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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: 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.
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
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
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
James
This seems to work fine for me.