jana
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.
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.
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
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
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
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
ASKER
thats good!...i'll check that out...!
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?
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
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..????
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..????
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
Have you tried using ADO for connection...or for that matter any other connection type?
M
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
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?
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
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.
My guess is that they don't match.
ASKER
Here are the screen shots. FYI: i'm not connecting to any server. Im using my local SQL instance.
DatasourceSetup.jpg
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.......
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?
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).
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
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
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.
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
Problem is centered around the COLLATE issue.... need to see SQL...to fix
ASKER
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.
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"?
ASKER
Ok...I'll look into it...thanx
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.
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.SetDataSour ce(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.
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.SetDataSour
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.
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?
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?
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.
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?
ASKER
SQL 2000 sp4...and yes, the ODBC configuration fuile has been tested with others.
Does "Verify Database" work?
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.
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)?
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
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.
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.
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.
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.
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?
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.
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.
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?
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.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanx...will do that!
ASKER
Even though I haven't had a solution for my problem, u deserve awarded for your patience and courteous.
Database -> Verify Database ?
Spykair