Link to home
Start Free TrialLog in
Avatar of jana
janaFlag for United States of America

asked on

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.
Avatar of Spykair
Spykair
Flag of South Africa image

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

Spykair
Avatar of Mike McCracken
Mike McCracken

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
Avatar of jana

ASKER

yes, the very first thing.
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
Avatar of jana

ASKER

thats good!...i'll check that out...!
Avatar of jana

ASKER

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?
Ramante:

Sorry for the confusion, ..that LINK was meant for ME...thanks..M
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..????
Avatar of jana

ASKER

Yes...I assign it in the Database section.  Also its in the DB of the SQL Instance
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
Avatar of jana

ASKER

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

My connection type is from Crystal; see attachment.

CR.Connection.jpg
So wait a minute....what version of Crystal are you using on your WORKSTATION?
Avatar of jana

ASKER

Crystal Report 11, Windows XP Sp2, Sql2000 sp4
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
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.

Avatar of jana

ASKER

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



DatasourceSetup.jpg
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.......

 
Are the TABLES , COLUMN NAMES....DATA TYPES...exactly the same as the MAIN SERVERS?
Avatar of jana

ASKER

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).
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
Avatar of jana

ASKER

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.
Is there anyway you can post the SQL here?
This sounds like a DB problem with you LOCAL DB...something is not LINKING correctly..???
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
Avatar of jana

ASKER

Ok...attach is the SQL query of CR

CR.SQL.Query.txt
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.
Avatar of jana

ASKER

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"?
Attached is an internal document describing the steps:



Crystal-Reports-Tutorial.pdf
Avatar of jana

ASKER

Ok...I'll look into it...thanx
Avatar of jana

ASKER

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.



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.

Avatar of jana

ASKER

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?

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?
Avatar of jana

ASKER

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.


SQL Server 2005?  Can you use/test the ODBC configuration file with anything else?
Avatar of jana

ASKER

SQL 2000 sp4...and yes, the ODBC configuration fuile has been tested with others.
Does "Verify Database" work?
Avatar of jana

ASKER

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.
Can you tell me how the DSN is configured for the local SQL Server?
Is there any reason why you didn't want to use the OLE DB connection type for SQL Server (better than ODBC)?
Avatar of jana

ASKER

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
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.
Avatar of jana

ASKER

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.

Avatar of jana

ASKER

Hi...I just finish reviewing the contents of your suggestion and my SQL Server.Local Connectivity is ok.
I am wondering what would happen if you recreate that one report that doesn't work.  Would that be too much to ask for?
Avatar of jana

ASKER

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.
Avatar of jana

ASKER

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?
Are you saying that a new report has the same issue?
Avatar of jana

ASKER

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.
I am out of ideas.  
Avatar of jana

ASKER

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?

ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

ASKER

Thanx...will do that!
Avatar of jana

ASKER

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