This topic explains how to create a routine to extract data from the system databases through a SQL script. The data will be processed and stored in an external database that can be accessed for creating custom reports and charts, for example.
The database and the data that will be extracted must be specified through a SQL script that will extract the data and reorganize it in an external database configured in the rm.service.exe.config file. This script processes and simplifies complex data, making it available in the external database that can later be accessed by providing its connection string, which is specified in the configuration file. This allows simpler SQL queries to be used to generate system reports and create charts in the Dashboard module, which would not be possible through SQL queries written to the complex structures of the system databases.
Note that the SQL script can only query and extract data from these databases and cannot make any changes to them. Therefore, only user accounts with read-only access can be specified in the connection string of the database that will be extracted. If a database is not specified in the script, the queries will be directed to the external database, by default. In addition, the databases that will be accessed must be registered in the system configuration file and the due permissions must be granted to ensure access. The history of the task indicates whether the task was successfully executed or if there were any errors.