We help IT Professionals succeed at work.

Crystal Reports XI - Using the Default Database

MichealMcLoughlin
on
Medium Priority
454 Views
Last Modified: 2012-05-05
This is an unusual one.

basically I have an application the connects to an SQL Server database,
the user can print off a number of reports against the data.

on all development pc's this works fine, but on 1 machine Crystal keeps trying to use the Default Database.

which is raising an error in the application as the stored procedure is not available in the default database.

any help on this is greatly appreciated.
Comment
Watch Question

Commented:
Hi
You could try this:

Database > Set Datasource location > Click the plus beside the table/view in the current data source area > 
Then click the Properties plus
Highlight the "Overridden Qualified Table Name" and press F2 (F2 allows you to edit the value for this), then press the End button on your keyboard and type the table name into this field.

Do this for all tables / views / sp.

I've had a few problems that sound a bit like yours and this has sorted it for me, but i must admit I dont know why or how.

HTH

Cootser

Author

Commented:
I really appreciate the prompt Reply but unfortunately this hasn't had any effect

I have re-installed crystall XI on the Machine twice but it still gives the same error.


Magic
Commented:
Hi Michael,

Sorry but that's about the limit of my tiny amount of knowledge of CR.  I hope someone else can help you.

Best of luck

Cootser

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Does the user of that PC have permissions to the correct database or the stored procedure?

mlmcc

Author

Commented:
The user is logging onto windows as an Administrator,  and is connecting to the database with the sa user (the SP Owner)


Author

Commented:
Cootser - Thanks for your time & help
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Have you tried changing the default database?

Do other reports cause trouble?

mlmcc

Author

Commented:
Basically, all of our reports were  in Crystal 8.5 (Approx 200 - 250 reports)

Any report modified and saved with Crystal XI has caused issues on this PC.

The Default database is used by an accounts package that we integrate to therefore can't be changed, (but changing it would  ultimately fix the issue).


Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
When you upgraded the reports did you verify the database?  Did you change the databse drivers to reflect the CR XI upgrade?

Is CR8.5 still installed on the machine?

mlmcc

Author

Commented:
Crystal 8.5, is installed on all machines along with Crystal XI but
(we need this to support older customers)
any report changed was verified
All necessary Drivers were changed in code


Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Don't know unless that machine is for some reason poicking up the wrong driver or a mismatch on dlls.

Any differences between machines?
  O/S?  Applications installed?

mlmcc

Commented:
MichealMcLoughlin, may I know how did you call up your report to display? it is via your own application? and what is your database connection type (OLEDB/ODBC etc)? If you call your report from your application, did you set the connection properly?

dylan

Author

Commented:
Both Operating Systems are XP Professional  
Both Machines are exact sames specs

The Reports are Called from our Own application the Connection used is and ODBC connection.

The connection is definitely set properly we have approx 200 customer sites using the software without any problems

Commented:
>> All necessary Drivers were changed in code

MichealMcLoughlin, do you mind to show us your code on how you setup the connection to report? I suspect with CRXI, you have to set PC's Name to the report so that it could pick up the right connection.

dylan

Author

Commented:
** Code from our CReport Object to logon to the server **

Public Function LogOnServer(ByVal ServerName As String, ByVal Database As String, Optional UId As String, Optional Pwd As String) As Boolean
    On Error GoTo ErrorHandle
    LogOnServer = False
    If Not IsMissing(UId) Then m_strUID = UId
    If Not IsMissing(Pwd) Then m_strPWD = Pwd
    m_strDatabase = Database
    m_strServerName = ServerName
    m_crystalApp.LogOnServer "pdssql.dll", ServerName, m_strDatabase, m_strUID, m_strPWD
    LogOnServer = True
    Exit Function
ErrorHandle:
    LogOnServer = False
    m_strLastError = Err.Description
    Exit Function
End Function

Commented:
MichealMcLoughlin, did you set the connection for each database object in your report (table/view/stored procedure)? In crystal report, other than setting up the connection for report, you will need to set connection info for each database object, or else they will reference to the old connection info. Below is my code in c#, please take a look and convert to VB, you may need to modify and merge it with you code, since you have your own "CReport" class. Just give it a try.

public void setConnection(string ServerName, string DatabaseName, string UserName, string Password)
{
     ConnectionInfo crConnectionInfo = new ConnectionInfo();

     crConnectionInfo.ServerName = ServerName;
     crConnectionInfo.DatabaseName = DatabaseName;
     crConnectionInfo.UserID = UserName;
     crConnectionInfo.Password = Password;

     CrystalDecisions.CrystalReports.Engine.Database crDatabase = rptDoc.Database;
     Tables crTables = crDatabase.Tables;

     foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
     {
          TableLogOnInfo crTableLogOnInfo = crTable.LogOnInfo;
          crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
          crTable.ApplyLogOnInfo(crTableLogOnInfo);
          crTable.Location = crTableLogOnInfo.TableName;
     }
}


dylan

Author

Commented:
One of Our Modules is .Net and I carry out a similar process
can this method be implememented in VB6
Commented:
certainly, I think this method is quite standard for all environment.

dylan

Author

Commented:
Cheers Dylan

I've have a crack at it tonight and let you know how i got on tomorrow

magic

Author

Commented:
After musch deliberation, i accidentally stumbled across the solution

The PC which was having the issue had a system dsn setup on it

with the same name as the Server

Crystal 11, was picking this up instead of the connection passed


Dylan + mlmcc thanks a million for all your help on this

Best Regards

Micheal (Magic)
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
That would be tough for us to figure out.

Glad to have helped

mlmcc

Author

Commented:
Given I know the solution to the problem now, it would have been virtually impossible to figure it out remotely

Apologies for that.

cheers again

Micheal

Commented:
Thanks Michael, glad u got there
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.