Symptom
- Error refreshing a report based on SQL Server.
- When refreshing a report based on MS SQL Server in Crystal Reports, it fails with the error:
"Database Connector Error: "HY000:[SAP Crystal Reports][ODBC SQL Server Wire Protocol driver][Microsoft SQL server]
Incorrect syntax near the keyword '<Table Name>'. [Database Vendor Code: 102 ]"
Environment
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
- SAP Crystal Reports 2020
- MS SQL Server
Reproducing the Issue
- In Crystal Reports, open a report based on MS SQL Server
- When refreshing the report, it fails with the error:
"Database Connector Error: "HY000:[SAP Crystal Reports] Database Vendor Code: 102]"
Cause
- The report used a Table name that is a reserved word in the database, and should not be used for object name in the MS SQL Server database, like: SELECT, GROUP, ALL, PUBLIC,...
For example: If a report contains a Table name called: Group, then when refreshing the report, it will fail because Group is a reserved word in MS SQL Server.
- For a list of MS SQL Sercer reserved word, see the Microsoft documentation at:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver15
Resolution
- To successfully refresh the report:
- Assign an alias to the Table name, that uses a reserved word; or
- Change how the SQL Query is generated, and add double quotes around the Table Name; or
- Contact your Database Administrator to rename the Table on the database side, to a name that does not use a reserved database word.
- Assign an alias to the Table name, that uses a reserved word.
- In Crystal Reports, open the report.
- Under the menu "Database", select "Database Expert"
- In the "Database Expert", under "Selected Tables", find the Table that uses a reserved word in the database.
- To give an alias name to the Table, right click on it, and select "Rename"
- Enter an alias Table name that is not a reserved word. For example: For a Table named: "Group", renamed it to "Group_1"
- Click "OK" to accept the change.
- Save the report.
- Change how the SQL Query is generated, and add double quotes around the Table Name
- To generate a SQL Query that add double quotations around Table Names, follow the steps found in the SAP Knowledge Base Article:
1217820 - Registry settings to configure how the SQL Query is generated in Crystal Reports
See Also
Keywords
CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020