Symptom
- No data returned.
- When filtering on a GUID, it returns no data.
- Filtering on the same GUID in MS SQL Server returns a result set.
- When reporting off MS SQL Server using an OLEDB connection, and filtering on a unique identifier, it returns no data in Crystal Reports.
- But when adding french braket around the GUID, like: '{9A784140-24B4-4482-85CC-FF9ACE5EDE75}', it returns data in Crystal Reports.
Environment
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
- MS SQL Server 2012
- MS SQL Server 2014
- MS SQL Server 2016
- MS SQL Server 2017
Reproducing the Issue
- In Crystal Reports, create a report off MS SQL Server using an OLEDB connection.
- Create a Record Selection formula that filter the data on a MS SQL Server GUID (uniqueidentifier), like:
{CustomerID} ='9A784140-24B4-4482-85CC-FF9ACE5EDE75'
- When refreshing the report, it returns no data, despite it returns data in MS SQL Server for the same GUID.
Cause
- In MS SQL Server, the GUID have a data type of: uniqueidentifier, but it is seen as a string data type via an OLEDB connection, and it is not converted back correctly by the OLEDB layer when sending the GUID, which is causing MS SQL Server to return no data. Therefore it returns no data due to the OLDB Provider incorrect conversion of the GUID.
Resolution
- To filter data on a MS SQL Server GUID database field, use an ODBC connection to connect to MS SQL Server.
Keywords
CR, SQL Sever GUID , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016