'Error 42S02, Vendor Code: 208' Dialog box

[Former Member]

Good afternoon,

I have a query regarding data sources and Crystal reports XI / 2008:

We have a SQL test db named 'TEST' and a live SQL db named 'LIVE'. Each is hosted on a different MS SQL 2005 server.

I setup an ODBC connection to both SQL servers on the user's PC (Windows XP SP3) using Admin. tools. I created a 'System DSN' and a 'User DSN' for testing purposes.

In CR 2008 (version 12.2.1.412) I created a new connection using ODBC (RDO) to the SQL servers using the previously configured ODBC data sources. These logged on successfully.

However, when a report's data source is changed from the TEST db to the LIVE db, a 'database connector error: '42S02: [Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object Name 'TEST.dbo.vRepPeople'.[Database Vendor Code: 208 ]' error dialog box pops up.

To troubleshoot this problem, we created a very basic report based on a few fields from a single view that exists in both SQL db. The error occured.

I have resolved the problem by setting CR to access the SQL servers via the CR OLE DB (ADO) connector - using the 'Microsoft OLE DB Provider for SQL Server' provider.

Could someone advise why this would resolve the problem? Is this due to OLE DB having improved software components compared to the older ODBC?

Also, this problem does not occur on CR 9 - it does occur on CR XI and 2008.

Regards

Edited by: kevin osborne on Nov 11, 2009 1:33 PM

[former_member11696 · Accepted solution]

Hi Kevin,

Lots of changes between CR 9 and 2008 in the database area.

It's not clear how or why you are using both a User and a System DSN but I would think this would be the cause. Typically we ask users to create System DSN's so all services and components have access to ODBC resources. Which kind of explains why the error, some dialog box is not available in one of the connections and likely the User DSN.

OLE DB uses completely different resources so you can compare one for one. ODBC has its standards and OLE DB has a different set of standards.

Thank you

Don

[ido_millet · Accepted solution]

Use Database, Set Location to set the UseDSNProperties property to True.

[Former Member · Accepted solution]

Hi again,

Many thanks for the information. I have now removed the User DSNs and still receive the error message when changing data sources using the System DSN.

I cannot change the 'Use Default DSN Properties' as the option appears to be read only. I accessed this through 'Database' > 'Set Database Location' > Right clicked on the connection and chose 'Properties' from the list. This displayed showed the expected connection properties but all the information cannot be edited.

Regards,

[former_member11696 · Accepted solution]

Hi Kevin,

Can you turn on ODBC tracing? It's a pain to get working, I find if I click on the tracing button, create the log file name, then click on the OK button, to close the ODBC Admin console. Open it up again and then click on the Start Tracing button it tends to work.

Look in the log to see if there are any errors that refer to this error. There may be others but that's just CR querying the driver to see what it supports. Also, try to stop any program that may be using ODBC also, if not you'll get all queries from those programs also.

If you can enable logging on the server that may help also to see if it's getting a permission denied error.

Thank you

Don

[Former Member · Accepted solution]

Hi Don,

I followed your suggestion and enabled ODBC tracing. From the log that was created, these are the only errors that correlate to the issue at hand:

crw32 974-924 ENTER SQLExecDirectW

HSTMT 071D2660

WCHAR * 0x02D6D174 [ -3] "select * from "TEST"."dbo"."vRepPeople" where 0=1\ 0"

SDWORD -3

crw32 974-924 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)

HSTMT 071D2660

WCHAR * 0x02D6D174 [ -3] "select * from "TEST"."dbo"."vRepPeople" where 0=1\ 0"

SDWORD -3

DIAG [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'TEST.dbo.vRepPeople'. (208)

This error occured when attempting to switch the data source to the LIVE data source. Interesting that the ODBC log is still looking for the data from the TEST db. This makes sense as there is no TEST.dbo.vRepPeople in the LIVE db so that object does not exist.

I would recommend to anyone having the same problem to try the newer OLE DB connection.

Regards,

[former_member11696 · Accepted solution]

Hi Kevin,

You need to watch the time stamp as to when you tried to connect etc. If you have Verify on first refresh or any of the Verify options enabled CR will try to connect using what is saved in the report. In this case the Test DB.

A little more debugging is required. But like you say, OLE DB is recommended. Microsoft does want to stop supporting ODBC.

From the looks of things you are using a Command Object: "select * from "TEST"."dbo"."vRepPeople" where 0=1\ 0"

Curious why you are using the where 0=1 statement? Select * should return all values.... but in this case you are telling it to never return anything....

Thanks again

Don

Edited by: Don Williams on Nov 16, 2009 7:27 AM

[Former Member · Accepted solution]

Hi Kevin,

I worked with Don on what I believe is the same problem.

If you haven't already done so, you may want to look at the following posting.

My last attempt at a resolve gave me the same error message as you received.

I am opening a support case this week and will share the results with you.

Leonard

[Former Member · Accepted solution]

Hi Leonard,

I read your posting with interest. there does indeed appear to be distinct similarities between the problems. Many thanks to Don and the other posters for their assistance. Unfortunately, I don't have the time to perform any further testing unless it is required to further troubleshoot the issue as a whole.

Our workaround of using OLE DB (ADO) instead of ODBC (RDO) works and I would recommend anyone else to try this as a fix.

I would be very interested to hear the results of your support case as CR 9 did not throw the error but the 'improved' CR XI and 2008 do!

Regards

[ido_millet · Accepted solution]

Just to confirm I'm seeing similar problems in 2008. I had posted a question about why setting UseDSNProperties to TRUE (in code) doesn't have the expected effect. Perhaps this is the same underlying problem.

[Former Member · Accepted solution]

Hi Kevin,

You may want to revisit the following post.

I have posted the results of my Support Case.

It is not good news.

Crystal maintains that this is 'By Design' and they are not going to change it.

Leonard

[former_member11696]

Manually set location for each table by selecting each table in the Set Location UI.

PG indicated this is by design.