Finding SQL columns in a Confirm Data Source

Products affected: Confirm

Issue

The SQL written for a Confirm Data Source is held in a Binary Large Object (BLOB) in the database but this is related to the values on the Columns tab on the Data Source screen.

One reason for identifying what is in Data Source SQL is if the Scheduled Report Agent runs for a long time you may want to find the name of the Data Source is causing the problem.  SQL can be used to look at the column names.

Cause

It is not possible to create a Data Source to extract the table and column names from the SQL held as a BOLB for another Data Source.

Resolution

UPDATED: December 27, 2019
Create a Data Source using the SQL below and qualify the last column, column_real, as contains “site_code” or something similar, it will list all those Data Sources and Reports containing that column.

SELECT
   data_source_column.data_source_key,
   data_source.data_source_name,
   data_source_column.column_number,
   data_source_column.column_name,
   data_source_column.column_real
FROM
   data_source,
   data_source_column
WHERE
   data_source.data_source_key = data_source_column.data_source_key;


Use this SQL to get table and column details and the name of Scheduled Reports that use them.
 
SELECT
   data_source_column.data_source_key,
   data_source.data_source_name,
   scheduled_report.sched_report_name,
   data_source_column.column_number,
   data_source_column.column_name,
   data_source_column.column_real
FROM
   data_source,
   scheduled_report,
   data_source_column
WHERE
   data_source.data_source_key = data_source_column.data_source_key AND
   data_source.data_source_key = scheduled_report.data_source_key;

This screenshot shows the fields on the Data Source screen that correspond to the column_name and column_real columns in the SQL above.

User-added image