3171274 - Error: "Failed to retrieve data from the database... Incorrect syntax near ','..." when refreshing a report based on a Command Object with a multiple values parameter in Crystal Reports

• Error: Incorrect syntax near ','
• When entering multiple values in a parameter, the report generates an error.
• When entering only one value in a multiple values parameter, the report refresh successfully.
• When refreshing a report based on a Command Object to MS SQL Server, and entering multiple values, Crystal Reports generate error like:
  
     "Failed to retieve data from the database.
  
       Database Connector Error: '42000:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
       Incorrect syntax near ','. [Database Vendor Code: 102]' "

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020
     
• MS SQL Server 2014
• MS SQL Server 2016
• MS SQL Server 2017
• MS SQL Server 2019

1. In Crystal Reports, create a report based on a MS SQL Server database using an ODBC connection.
     
2. In a Command Object, enter a custom SQL Query.
        
3. Still in the Command Object, create a parameter, and check the option "Allow multiple values"
       
4. In the customer SQL Query enter in the WHERE clause of the SQL Query the parameter surrounded by parenthesis, like:
   
         SELECT Country, CustomerName
         FROM Customer
         WHERE Country IN ({?MyParameter})
      
5. When refreshing the report, and entering multiple values in the parameter, it fails with the error.

• When setting a parameter to multiple values in a Command Object, it substitute the parameter in the custom SQL Query, by a string with the values entered in the parameter, surrounded by parenthesis like:
  
       ('France', 'Germany')
     
• Since the parameter is already surrounded by parenthesis in the custom SQL Query written, like:
  
       WHERE Country IN ({?MyParameter})
  
  Then the SQL Query sent to the database will be like:
  
       WHERE Country IN (('France', 'Germany'))
  
  And the double parenthesis is not a valid syntax for MS SQL Server, and causes the error when entering multiple values in the parameter.

• Remove the parenthesis around the parameter in the custom SQL Query of the Command Object for the report in Crystal Reports:
    
1. In Crystal Reports, open the report.
    
2. Under the menu "Database", select "Database Expert"
     
3. In "Database Expert", under "Selected Tables", right click on the Command object, and select "Edit Command"
     
4. If prompted, logon to your database.
     
5. In the window "Modify Command", edit the SQL Query, and remove the parenthesis around the parameter set to use multiple values.
     
6. Click "OK" to accept the change, and save your report.

CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020