How to find out which SQL queries were not executed

When you use database reporting, to maintain a log of each message going through the system, you might want to check if all SQL commands were executed properly on your database. Ozeki 10's SQL reporting feature provides a user interface to see which records are waiting to be executed, and which ones failed. This document gives you information on how monitor the execution of SQL queries in your reporting system.

Step 1 - Open the reporting system

The reporting system can save messages going through the system into different sources, such as databases, txt files, etc. The most popular choice is using an SQL database server, such as Microsoft SQL server of MySQL to save a log of each message. Database servers are great, because they can deal with a great amount of data, which is a must for high-capacity systems.

Figure 1 - Open SQL reporting

Step 2 - Open the details page of the database link

The details page of the SQL database link allows you to configure the database connection, to view events and to check out the SQL commands waiting to be executed and the ones that could not be executed.

Figure 2 - Click details

Step 3 - Select the "Failed" tab

The Failed tab is where you can see the list of SQL commands waiting to be executed and the list of commands that were not executed. You might notice that multiple commands are grouped together. This is called batch processing. Most database servers are not able to handle several hundreds of transactions per second. By grouping multiple INSERT and UPDATE statements into a single SQL request, the performance can be greatly improved. If such a batch request fails, the whole request is saved in the FAILED list.

Figure 3 - Select the "Failed" tab

Step 4 - Delete the "Failed" queries

On this page you may export the failed queries into an .sql text file, which you can use for later execution on your database. You may also want to delete these queries. This can be useful, if you made a mistake when you created the original SQL templates and there is no chance for these queries to be executed.

Figure 4 - Delete the "Failed" queries from .sql text file

More information