INTERNAL- Connecting a site in TIBCO Data Virtualization using Business Directory fails with an Invalid SQL error

Products	Versions
TIBCO Data Virtualization	8.0 and earlier

Description

In TIBCO Data Virtualization, adding a new site in Business Directory fails with the following error:

"ERROR [SiteRefreshTask Site refresh task for po-tdvdev01] 2019-02-20 12:16:54.339 +0000 SiteApi - User Exception Occurred
com.compositesw.common.UserException: An exception occurred when executing the following query: "SELECT {OPTION CASE_SENSITIVE="TRUE"} 
.....
.....
WHERE DOMAIN_TYPE != 'COMPOSITE' AND DOMAIN_TYPE != 'DYNAMIC')". 
Cause: Unable to retrieve result from data source "/system/datasources/repository" during query execution. "

Issue/Introduction

Connecting a site in TIBCO Data Virtualization using Business Directory fails with an Invalid SQL error

Resolution

Set the value for Case Sensitivity to 'False' found in TDV Studio > Administration tab > Configuration > Server > SQL Engine > SQL Language. The default value for TDV is 'True'.
Now, try adding a new site.

Note: This is a global configuration change which will be applied to all the queries. To avoid such change, add a new site with TDV 8.1

Additional Information

Here is the complete error message:

"ERROR [SiteRefreshTask Site refresh task for po-tdvdev01] 2019-02-20 12:16:54.339 +0000 SiteApi - User Exception Occurred
com.compositesw.common.UserException: An exception occurred when executing the following query: "SELECT {OPTION CASE_SENSITIVE="TRUE"} 
RTRIM(DATASOURCE_NAME) DATASOURCE_NAME, 'CATALOG' METADATA_TYPE, COUNT(*) METADATA_COUNT FROM ALL_CATALOGS WHERE DATASOURCE_ID IN ( select DATASOURCE_ID from ALL_DATASOURCES where owner <> 'system' and datasource_type <> 'VirtualWsdl' ) GROUP BY RTRIM(DATASOURCE_NAME) UNION SELECT {OPTION CASE_SENSITIVE="TRUE"} RTRIM(DATASOURCE_NAME) DATASOURCE_NAME, 'SCHEMA' METADATA_TYPE, COUNT(*) METADATA_COUNT FROM ALL_SCHEMAS WHERE DATASOURCE_ID IN ( select DATASOURCE_ID from ALL_DATASOURCES where owner <> 'system' and datasource_type <> 'VirtualWsdl' ) GROUP BY RTRIM(DATASOURCE_NAME) UNION SELECT {OPTION CASE_SENSITIVE="TRUE"} RTRIM(DATASOURCE_NAME) DATASOURCE_NAME, 'TABLE' METADATA_TYPE, COUNT(*) METADATA_COUNT FROM ALL_TABLES WHERE DATASOURCE_ID IN ( select DATASOURCE_ID from ALL_DATASOURCES where owner <> 'system' and datasource_type <> 'VirtualWsdl' ) GROUP BY RTRIM(DATASOURCE_NAME) UNION SELECT {OPTION CASE_SENSITIVE="TRUE"} RTRIM(DATASOURCE_NAME) DATASOURCE_NAME, 'COLUMN' METADATA_TYPE, COUNT(*) METADATA_COUNT FROM ALL_COLUMNS WHERE DATASOURCE_ID IN ( select DATASOURCE_ID from ALL_DATASOURCES where owner <> 'system' and datasource_type <> 'VirtualWsdl' ) GROUP BY RTRIM(DATASOURCE_NAME) UNION SELECT {OPTION CASE_SENSITIVE="TRUE"} RTRIM(DATASOURCE_NAME) DATASOURCE_NAME, 'PROCEDURE' METADATA_TYPE, COUNT(*) METADATA_COUNT FROM ALL_PROCEDURES WHERE DATASOURCE_ID IN ( select DATASOURCE_ID from ALL_DATASOURCES where owner <> 'system' and datasource_type <> 'VirtualWsdl' ) GROUP BY RTRIM(DATASOURCE_NAME) UNION SELECT {OPTION CASE_SENSITIVE="TRUE"} RTRIM(DATASOURCE_NAME) DATASOURCE_NAME, 'WSDL_OPERATION' METADATA_TYPE, COUNT(*) METADATA_COUNT FROM ALL_WSDL_OPERATIONS WHERE DATASOURCE_ID IN ( select DATASOURCE_ID from ALL_DATASOURCES where owner <> 'system' and datasource_type <> 'VirtualWsdl' ) GROUP BY RTRIM(DATASOURCE_NAME) UNION SELECT NULL DATASOURCE_NAME, 'DOMAIN' METADATA_TYPE, COUNT(*) METADATA_COUNT FROM ALL_DOMAINS WHERE DOMAIN_TYPE != 'COMPOSITE' AND DOMAIN_TYPE != 'DYNAMIC' UNION SELECT NULL DATASOURCE_NAME, 'GROUP' METADATA_TYPE, COUNT(*) METADATA_COUNT FROM ALL_GROUPS WHERE DOMAIN_ID IN ( SELECT DOMAIN_ID FROM ALL_DOMAINS WHERE DOMAIN_TYPE != 'COMPOSITE' AND DOMAIN_TYPE != 'DYNAMIC') UNION SELECT NULL DATASOURCE_NAME, 'USER' METADATA_TYPE, COUNT(*) METADATA_COUNT FROM ALL_USERS WHERE DOMAIN_ID IN ( SELECT DOMAIN_ID FROM ALL_DOMAINS WHERE DOMAIN_TYPE != 'COMPOSITE' AND DOMAIN_TYPE != 'DYNAMIC')". Cause: Unable to retrieve result from data source "/system/datasources/repository" during query execution. "

Welcome to "KB Articles"