Troubleshooting
Problem
When executing blocks of SQL statements, all SQL within that block takes the success value of the first statement. For example if there are 3 statements in the block, if the first succeeds all three will show succeeded=1. If the first fails all three will show succeeded=0. This is regardless of whether the last two statements succeed or fail on the database.
Cause
When multiple statements are executed in a single block it is complex and time consuming to analyze the response for each individual statement. In order to have reasonable performance when monitoring traffic the success of the first statement is used to mark the whole block.
Environment
This behavior has been reported specifically for MSSQL Server Management Studio and Oracle PLSQL but could apply to any database client executing SQL in blocks.
Diagnosing The Problem
In order for the Succeeded column to be populated you must select 'Log Records Affected" in the inspection engine configuration of the collector. This applies to all versions higher than v8.2 p150.
1. GUI->Administration Console->Configuration->Inspection Engines.
2. Select Log Records Affected
3. Apply and restart the inspection engine from that GUI page
4. In the CLI run:
- restart inspection-core
Example in MS SQL of command sequences and expected results. The SQL in each table is executed in one block.
1. Starting with a failed statement.
| Order in the block | SQL statement | Output in MS SQL | Value of Succeded column in Guardium report |
| 1 | update table set column=1 where column=2; | Failed - e.g. with permission denied. | 0 |
| 2 | select * from table; | Succeeded | 0 |
| Order in the block | SQL statement | Output in MS SQL | Value of Succeded column in Guardium report |
| 1 | select * from table; | Succeeded | 1 |
| 2 | update table set column=1 where column=2; | Failed | 1 |
3. Using "GO" in the SQL block to break it into separate sections will result in each section being treated as a block. Examples 1 and 2 apply to multiple statements in between GOs.
| Order in the block | SQL statement | Output in MS SQL | Value of Succeded column in Guardium report |
| 1 | select * from table; | Succeeded | 1 |
| 2 | GO | n/a | n/a |
| 3 | update table set column=1 where column=2; | Failed | 0 |
| 4 | select * from table; | Succeeded | 0 |
Resolving The Problem
This behavior is expected. It is a known limitation of the current product design, implemented in order to improve performance.
To request a change to that design you can open a request for enhancement (RFE) to product management.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21691148