How can I fix Failed to retrieve data from database [Database Vendor Code: 446] Crystal Repors 11

jana
jana used Ask the Experts™
on
We are running Crystal Reports 11.  

There is a specific report when ran in the server (server has a copy a of CRv11) it runs perfectly.  I can modifiy it and re-run it.  But when I copy it to my workstation and run it, it gives me a "Failed to retrieve data from database ...  [Database Vendor Code: 446] " message.

My workstation has its on SQL as does the server.

For connection I use ODBC.

The thing is that when I use the Server ODBC, my copy of Crystal works, but if I use my local ODBC, it doesn't and gives the message above.

I checked both SQL and are the same version version 2000 with latest service.  However onbe is developer and the othe personal edition.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Have you tried to verify the database ?
Database -> Verify Database ?

Spykair
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What do you mean the personal edition?

Are you running in the Crystal designer?

You need to change the report to use the other data source.
If you are in the Crystal designer you can use the DATABASE --> SET DATASOURCE LOCATION

mlmcc

Author

Commented:
yes, the very first thing.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
It has to do with your LOGIN Credentials to the DATABASE. HOW is your workstation setup to login to the DB? Windows Login? DB Login?

I would suspect that you'd need to make sure the login is set to DB login and make sure you have this setup correctly with your DBA.

M

Author

Commented:
thats good!...i'll check that out...!

Author

Commented:
I understand that when u say "set to DB login ", u refer to login to the DB thru SQL, not Window Auth.?

I verified the way CR report logges on, and its the following:

I use Database>>Set Datasource Location>>inlcude DB thru ODBC>>and click "Update".  Also I went to the DB in SQL Enterprise and assured an exisiting User ID is in the DB.

Is this correct or am I missing sometinh?

Hi mlmcc, I looked at your link and didn't quite see how it would help.  Would u pint out exactl;y what u want me to see?
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Ramante:

Sorry for the confusion, ..that LINK was meant for ME...thanks..M
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Ramante,

So you are confirming that you have login USERID and PASSWORD saved inside the Crystal Report. AND you have confirmed the login USERID and PASSWORD in the SQL Database..????

Author

Commented:
Yes...I assign it in the Database section.  Also its in the DB of the SQL Instance
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Are you sure your workstation is configured correctly for ODBC?

Have you tried using ADO for connection...or for that matter any other connection type?

M

Author

Commented:
yes...I use it for my Microsoft Dynamics products; all are thru ODBC.

My connection type is from Crystal; see attachment.

CR.Connection.jpg
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
So wait a minute....what version of Crystal are you using on your WORKSTATION?

Author

Commented:
Crystal Report 11, Windows XP Sp2, Sql2000 sp4
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Please do this:

GO to:

START >> CONTROL PANEL >> ADMINSTRATIVE TOOLS >> DATA SOURCES >> SYSTEM DSN TAB

Post Screenshot of above.

Also, then go into CONFIGURE and post screenshot of your ODBC datasource...

Thanks
M
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Basically what you need to do is have your WORKSTATIONS ODBC CONNECTION ......mirror... OR match the SERVER'S ODBC CONNECTION.

My guess is that they don't match.

Author

Commented:
Here are the screen shots.  FYI: i'm not connecting to any server.  Im using my local SQL instance.



DatasourceSetup.jpg
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Well then that is basically your problem then...


In that case I can tell you right off...that UNLESS your "local" SQL Server DB's are configured EXACTLY like the Main Server's ...then you're gona have connections issues.

The report is seeking to connect to the SERVER's DB's and can't find them.......

 
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Are the TABLES , COLUMN NAMES....DATA TYPES...exactly the same as the MAIN SERVERS?

Author

Commented:
I'm not connected to any server.  I'm running the report in my LOCAL SQL instance/install.  I have done this before.

In Crystal, I use Database>>Set Datasource Location>>inlcude DB thru ODBC>>and click "Update" in order to use my local instance.  The I clic on Verify database to make sure all of.

Also, the TABLES , COLUMN NAMES....DATA TYPES are exactly the same becuase backed up the DB from the server and restored exactly in the same file location as the server (folder names).
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
TEST please.

Take the SQL from the Crystal Report...and try and run in IN SQL SERVER QUERY ANALYZER. What happens?

If it runs OK in SQL Query Analyzer...then take that same SQL from the Crystal Report and use it to create a NEW report using your workstation copy of CR. When creating the report, go to the DATASOURCE and copy/paste the SQL into the ADD COMMAND line at the datasouce. The result would be a Virtual TABLE that can be used to create the NEW report. Once the SQL is accpeted...place a few of the columsn of data on the Crystal Report..and tell me what happens.

Thanks
M

Author

Commented:
Ok..I opened the report, I verified that its looking into the correct DB & tables with "Set Location", I click on Verify Database" and all ok.  I then goto Database>>Show SQL Query, copy it to my SQL Analyzer, and I get this error:

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Is there anyway you can post the SQL here?
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
This sounds like a DB problem with you LOCAL DB...something is not LINKING correctly..???
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Might actually have something to do with ONE version of SQL being Developer and one being Professional...although I"m not sure.

Problem is centered around the COLLATE issue.... need to see SQL...to fix

Author

Commented:
Ok...attach is the SQL query of CR

CR.SQL.Query.txt
Most Valuable Expert 2012
Top Expert 2008

Commented:
There are a lot of comments, and I would like to try a different approach, if you have the ability to change directions:  map the report to an XSD schema document, and push an external DataSet/DataTable as the report source.

Author

Commented:
Can u give more detail of with "map the report to an XSD schema document" and "ush an external DataSet/DataTable as the report source"?
Most Valuable Expert 2012
Top Expert 2008

Commented:
Attached is an internal document describing the steps:



Crystal-Reports-Tutorial.pdf

Author

Commented:
Ok...I'll look into it...thanx

Author

Commented:
Ok...i checked it out...unfortunately can't find those command in my CR version (I have v11).  I viewed your PDF and i dont understanding completely.  In the last page I assume u want to add new data source and then a field to my report.  I have tried to find similar command but can't find it.

I would like to point out that the report I'm having problem with, only does it in my notebook when I use "Set DataSource Location..." and point to a copy of the DB in my local SQL instance.  This same report when pointing to the Server SQL instance works perfectly, but when I point the DataSpource to my local SQL instance (using my ODBC), I get the error.



Most Valuable Expert 2012
Top Expert 2008

Commented:
1) I don't like the maintenance headaches with the "pull" method from a database, and the way that Crystal Reports hard codes the connections.

2) I will have to look at home in CR XI to see what the difference is.

3) If you create a report, and bind it to an XSD schema document, then you can pass in a DataSet that has the same elements, using ReportDocument.SetDataSource(DataSet).

4) With the push method, you don't need to store any connection information inside the report, so it is easier to have your application get the information from the database using a connection string, and then push the data to the report.  You wouldn't need to define any Crystal Reports parameters in this case either.

Author

Commented:
The Push Method sound super interesting and obviously I have to learn a lot about! How can align my search to get info to learn that method?

Nevertheless, if I use the push method, I have to change the reports with the problem (and its quite extensive).  This method will be great for new reports (after I get to learn it properly).  But to solve my problem as it is, using the current SQL/ODBC connection (because the same report works in other PC), is there any other suggestions?

Most Valuable Expert 2012
Top Expert 2008

Commented:
That is a vague error that doesn't describe why you can't pull data from ODBC, and you have tried a lot.  Can you review your problem with me, because there is a lot of noise from all the comments that didn't help?

Author

Commented:
Thanx TheLearnedOne.  The problem I'm having is:

1. I have a CR report version 11 all finish & dandy
2. The report is set to access the DB thru ODBC and the DB is in a MS-SQL instance
    located at the server
3. The ODBC in all PC points to the server MS-SQL
4. The report runs perfect in any PC, even in my PC, with ODBC pointing to the server
5. Problem: when I change the ODBC in my PC and point to my local MS-SQL instance,
    I get the Failed error.  This only happens in my PC and when pointing to my SQL

My MS-SQL in my PC is used always.  I have different DBs from different clients, etc,; what I mean is a working SQL environment.

Hope this helps.


Most Valuable Expert 2012
Top Expert 2008

Commented:
SQL Server 2005?  Can you use/test the ODBC configuration file with anything else?

Author

Commented:
SQL 2000 sp4...and yes, the ODBC configuration fuile has been tested with others.
Most Valuable Expert 2012
Top Expert 2008

Commented:
Does "Verify Database" work?

Author

Commented:
Yes...the process of Verify Database works.

Also, when I run the report in my PC, pointing to my local SQL, it takes me up to where it asks me for the arguments or options design to ask the user.  When clicking OK, it gives me the Failed error.
Most Valuable Expert 2012
Top Expert 2008

Commented:
Can you tell me how the DSN is configured for the local SQL Server?
Most Valuable Expert 2012
Top Expert 2008

Commented:
Is there any reason why you didn't want to use the OLE DB connection type for SQL Server (better than ODBC)?

Author

Commented:
Both DSN is configure the same in ODBC in Local & Server (System DSN) .  When I want to use Server, I change content of "Server:", when local, I enter "My PC name).  The config is attached in screen shots.

As for OLE DB, no specific reason.  Just is what we use.  I'm not familiar with OLE (I'm around average in these stuff, trying to fix current stuff).
Config01.jpg
Config02.jpg
Config03.jpg
Config04.jpg
Config05.jpg
Config06.jpg
Most Valuable Expert 2012
Top Expert 2008

Commented:
1) Did I tell you how much I dislike the "pull" method?

2) I don't see anything from the configuration, on the surface.

3) Here are some things to check:

SQL Server Connectivity
http://www.sqljunkies.ddj.com/HowTo/2E1101E0-D5C1-4DBD-A398-FE485DFA439B.scuk

Check If SQL Server Is Listening Correctly
Verify that SQL Server is listening on Named Pipes, TCP/IP, or any other protocol that you are using. To verify it, open the current SQL Server Error Log. You should see lines similar to the following:

2002 12 14 09:49:36.17 server SQL server listening on TCP, Shared Memory, Named Pipes.
2002 12 14 09:49:36.17 server SQL server listening on 192.168.1.5:1433, 127.0.0.1:1433.

4) You might try to test the connection with "Named Pipes", instead of TCP.

Author

Commented:
When u say "I don't see anything from the configuration, on the surface", can u send me the instruction(s) u need me to run so I can give u the configuration?

Question: I have other Crystal Reports that works perfectly in my PC, I also use the same ODBC for my financials applications (pointing to my local SQL), etc. and the only problem I have is with that specific CR report.  You think is my SQL instance?

I will try your suggestion and I greatly appreciate your efforts.

Author

Commented:
Hi...I just finish reviewing the contents of your suggestion and my SQL Server.Local Connectivity is ok.
Most Valuable Expert 2012
Top Expert 2008

Commented:
I am wondering what would happen if you recreate that one report that doesn't work.  Would that be too much to ask for?

Author

Commented:
yes....

This is a strange situation, very ...this problem is only  this report...i use my SQL instance a lot, true working area ... i use other products related to SQL and all worked.... strange this this this with report.

Author

Commented:
Hi CRXIuser2005 and TheLearnedOne...I just got back from a business trip and couldn't followup.  Unfortunately, I still have the sames problem and followed all your suggestions.  Anything else we can do?
Most Valuable Expert 2012
Top Expert 2008

Commented:
Are you saying that a new report has the same issue?

Author

Commented:
no, no...i meant as a followup...but to answer your last thread of "01.09.2009 at 05:35AM PST, ID: 23335280", I didn't recreate the report because its pretty complicated.  Since the report works in all other PC, not mine.
Most Valuable Expert 2012
Top Expert 2008

Commented:
I am out of ideas.  

Author

Commented:
Thanx...u been great.

I don't know if one of the EE advisors monitors these questios, but what shold I do woth this open question I have, do u know?

Most Valuable Expert 2012
Top Expert 2008
Commented:
I would delete this question, and if you are still having a problem, then reask it again.  When you have a question like this, with a lot of comments, no one else would even want to get on board.  You could use the information gathered here, to describe a better question, and hopefully you would catch the interest of the expert that would have the experience to help you.

Author

Commented:
Thanx...will do that!

Author

Commented:
Even though I haven't had a solution for my problem, u deserve awarded for your patience and courteous.

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