Crystal Reports XI - Using the Default Database

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.
LVL 2
MichealMcLoughlinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CootserCommented:
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
MichealMcLoughlinAuthor 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
CootserCommented:
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
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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

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


MichealMcLoughlinAuthor Commented:
Cootser - Thanks for your time & help
mlmccCommented:
Have you tried changing the default database?

Do other reports cause trouble?

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


mlmccCommented:
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
MichealMcLoughlinAuthor 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


mlmccCommented:
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
dylanyeeCommented:
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
MichealMcLoughlinAuthor 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
dylanyeeCommented:
>> 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
MichealMcLoughlinAuthor 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
dylanyeeCommented:
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
MichealMcLoughlinAuthor Commented:
One of Our Modules is .Net and I carry out a similar process
can this method be implememented in VB6
dylanyeeCommented:
certainly, I think this method is quite standard for all environment.

dylan

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MichealMcLoughlinAuthor Commented:
Cheers Dylan

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

magic
MichealMcLoughlinAuthor 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)
mlmccCommented:
That would be tough for us to figure out.

Glad to have helped

mlmcc
MichealMcLoughlinAuthor 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
CootserCommented:
Thanks Michael, glad u got there
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.