Crystal Reports 2020 - SQL Syntax Error

[cr-user-gr]

I recently upgraded from Crystal Reports 2013 (version 14.1.x) to 2020 (version 14.3.x). One of the reports I run doesn’t seem to work in Crystal Reports 2020. I am getting the error “SQL Syntax error” when I try refreshing the report. The exact same .rpt file refreshes fine in Crystal Reports 2013.

The SQL query generated in Crystal Reports 2020 is syntactically correct (I've ran it through SQL syntax checkers). While reviewing the SQL query generated in CR 2020 and CR 2013, I noticed the FROM clause generated in CR 2020 has all tables joined with INNER JOIN whereas the SQL query generated in CR 2013 does not.

I started looking into this a bit more and did come across this post from 2010. The post is for Crystal Reports XI (version 11.5). It mentions a registry key and "SQLServerJoinBuilder"="adbc" (was this a typo for "odbc"?). I am almost positive this should resolve my issue, but that post doesn't have much details.

I also found KB 1217820 - Registry settings to configure how the SQL Query is generated in Crystal Reports. It does provide more details, but I just can't seem to figure out what registry key I need. Since I am using Crystal Reports 2020, I do know I need:

[HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\JoinBuilder]

I did have to create all the keys starting from 'Database'. After this, steps 7 and 8 are unclear to me. Do I need to add the string value SQLServerJoinBuilder and then set it's value to match my ODBC Driver File Name from step 4? I am using Rocket Software's U2 ODBC Drivers and I see "U2ODBC.DLL" in the file name. Should I create the string key and values shown below?

 SQLServerJoinBuilder = U2ODBC 

If that's the case, this isn't working for me. Or am I supposed to create the string key U2ODBCJoinBuilder? If so, what value should it be set to?

I've tried many different things with this registry path, but nothing seems to resolve my issue. Am I on the right track? Is this what I need to resolve my issue? What am I missing with these registry keys/values?

Thanks,

GR

[former_member11696]

Hi GR,

From the list of Join Types you need to use the one that the U2ODBC driver supports.

@Dell the list of join types is typical, it doesn't mean you need the Clients name in the value, it's only directing CR to use that type of Join syntax. Left over from early CR 8.5 days...

Looking at this: https://docs.rocketsoftware.com/nxt/gateway.dll/RKBnew20/u2%20clients%20and%20apis/previous%20versio...

It appears the U2ODBC client uses the same as SQL Server.

Possibly contact U2 and ask them for help.

[DellSC]

KB 2165260 is the master list of registry entries. Based on that, I believe you need to create the string key if you're connecting to SQL Server.

SQLServerJoinBuilder = U2ODBC 

However, you probably also need to make sure that the path to the U2ODBC.dll file is included in the computer's PATH variable. You need to make sure that this is a 64-bit database driver and that you have a 64-bit ODBC connection configured in order to use this with Crystal 2020.

Also, if you're connecting to a database other than SQL Server, you'll need to use a different string key - see the KB for more information.

Finally, looking at the Product Availability Matrix (PAM) for Crystal 2020, I don't see that the Rocket driver is supported by the software. That doesn't mean it won't work, but it doesn't guarantee it will. If you're running on Windows and you're connecting to SQL Server, your best bet will be to try to use either the SQL Server Native Client 11 (sqlncli) or MS SQL Server ODBC Client 13 (msodbcsql13) to connect.

-Dell

[cr-user-gr]

Hi Dell,

Thank you for responding to my question.

This is where I'm a bit fuzzy. My report is pulling data from a UniData database. Does that mean I should name the string key U2ODBCJoinBuilder instead of SQLServerJoinBuilder? If so, what value should this string have? More on this below as I go over steps 7 and 8.

I've verified that U2ODBC.DLL's path is in the environment PATH variable.

Yes, I am using the 64-bit UniData ODBC drivers. I can successfully run other reports in Crystal Reports 2020.

I am still getting the "SQL Syntax Error" for this particular report.

You are correct. I don't see Rocket Software's UniData in the PAM for Crystal Reports. That being said, so far all my existing reports querying the UniData database work fine with the exception of this one report which pulls data from 6 different tables. This report runs fine in Crystal Reports 2013 but not in Crystal Reports 2020. The main difference I see between CR 2013 and CR 2020 is how each build the SQL query. I am hoping I can get the right registry key and value to resolve my issue in CR 2020.

You are right, since Rocket's drivers are not officially supported according to the PAM, there's no guarantee this will work. However, I am not convinced I am using the right registry key and value. I'm not ready to thrown in the towel. Here's why the instructions are not clear to me:

Step 7 in the KB 2165260 says:

"Add a String Value and enter the Registry Key Name corresponding to the syntax you want to change. For example: Add the registry key: SQLServerJoinBuilder"

Do I want SQLServer as the corresponding syntax to change? Should it be SQLServerJoinBuilder? After all, the error I get does say "SQL Syntax Error". What about the "String Value" mentioned in step 7? What should the value be?

Step 8 says:

"Add to the Registry Key, the ODBC Driver File Name you took note of in step 4. ( without the .DLL extension )"

Am I supposed to create another key? Or does step 8 mean the string value?

Is this what I should have?

Whether the string key should be named SQLServerJoinBuilder or U2ODBCJoinBuilder, neither of these with the value U2ODBC resolve my issue.

Please advise.

GR

[DellSC]

Unfortunately, I don't think you're going to be able to do this. The KB article that I mention above provides only these options for the registry key:

• OJSyntax
• StarEqualJoinBuilder
• SQLServerJoinBuilder
• InformixJoinBuilder
• OracleJoinBuilder
• OracleJoinBuilder2
• SybaseJoinBuilder
• DB2JoinBuilder
• LotusNotesJoinBuilder
• CannotSupportExternalJoins

You may end up having to re-write the report using a Command, where you create the SQL Select statement yourself instead of having Crystal create it for you. If you decide you want to do that, here's some additional information about how to work with commands:

https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell