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

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.
rayluvsAsked:
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.

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

Spykair
0
mlmccCommented:
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
0
rayluvsAuthor Commented:
yes, the very first thing.
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

MIKESoftware Solutions ConsultantCommented:
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
0
rayluvsAuthor Commented:
thats good!...i'll check that out...!
0
rayluvsAuthor 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?
0
MIKESoftware Solutions ConsultantCommented:
Ramante:

Sorry for the confusion, ..that LINK was meant for ME...thanks..M
0
MIKESoftware Solutions ConsultantCommented:
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..????
0
rayluvsAuthor Commented:
Yes...I assign it in the Database section.  Also its in the DB of the SQL Instance
0
MIKESoftware Solutions ConsultantCommented:
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
0
rayluvsAuthor 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
0
MIKESoftware Solutions ConsultantCommented:
So wait a minute....what version of Crystal are you using on your WORKSTATION?
0
rayluvsAuthor Commented:
Crystal Report 11, Windows XP Sp2, Sql2000 sp4
0
MIKESoftware Solutions ConsultantCommented:
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
0
MIKESoftware Solutions ConsultantCommented:
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.

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



DatasourceSetup.jpg
0
MIKESoftware Solutions ConsultantCommented:
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.......

 
0
MIKESoftware Solutions ConsultantCommented:
Are the TABLES , COLUMN NAMES....DATA TYPES...exactly the same as the MAIN SERVERS?
0
rayluvsAuthor 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).
0
MIKESoftware Solutions ConsultantCommented:
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
0
rayluvsAuthor 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.
0
MIKESoftware Solutions ConsultantCommented:
Is there anyway you can post the SQL here?
0
MIKESoftware Solutions ConsultantCommented:
This sounds like a DB problem with you LOCAL DB...something is not LINKING correctly..???
0
MIKESoftware Solutions ConsultantCommented:
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
0
rayluvsAuthor Commented:
Ok...attach is the SQL query of CR

CR.SQL.Query.txt
0
Bob LearnedCommented:
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.
0
rayluvsAuthor 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"?
0
Bob LearnedCommented:
Attached is an internal document describing the steps:



Crystal-Reports-Tutorial.pdf
0
rayluvsAuthor Commented:
Ok...I'll look into it...thanx
0
rayluvsAuthor 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.



0
Bob LearnedCommented:
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.

0
rayluvsAuthor 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?

0
Bob LearnedCommented:
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?
0
rayluvsAuthor 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.


0
Bob LearnedCommented:
SQL Server 2005?  Can you use/test the ODBC configuration file with anything else?
0
rayluvsAuthor Commented:
SQL 2000 sp4...and yes, the ODBC configuration fuile has been tested with others.
0
Bob LearnedCommented:
Does "Verify Database" work?
0
rayluvsAuthor 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.
0
Bob LearnedCommented:
Can you tell me how the DSN is configured for the local SQL Server?
0
Bob LearnedCommented:
Is there any reason why you didn't want to use the OLE DB connection type for SQL Server (better than ODBC)?
0
rayluvsAuthor 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
0
Bob LearnedCommented:
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.
0
rayluvsAuthor 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.

0
rayluvsAuthor Commented:
Hi...I just finish reviewing the contents of your suggestion and my SQL Server.Local Connectivity is ok.
0
Bob LearnedCommented:
I am wondering what would happen if you recreate that one report that doesn't work.  Would that be too much to ask for?
0
rayluvsAuthor 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.
0
rayluvsAuthor 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?
0
Bob LearnedCommented:
Are you saying that a new report has the same issue?
0
rayluvsAuthor 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.
0
Bob LearnedCommented:
I am out of ideas.  
0
rayluvsAuthor 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?

0
Bob LearnedCommented:
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.
0

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
rayluvsAuthor Commented:
Thanx...will do that!
0
rayluvsAuthor Commented:
Even though I haven't had a solution for my problem, u deserve awarded for your patience and courteous.
0
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
DB Reporting Tools

From novice to tech pro — start learning today.