Troubleshooting
Problem
I am using the Guardium Query Rewrite feature to rewrite queries on MSSQL Server database.
After executing a query on MSSQL that should be rewritten, an MSSQL error appears on the screen like:
Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server
The results of the query are not shown. The error only appears for long queries with many characters.
Cause
The returned packet from Query Rewrite to the database is larger than the maximum allowed size of network packets for the database.
Diagnosing The Problem
1. In MSSQL, run a very long sql that will be rewritten by existing query rewrite rule, for example:
SELECT TOP 1000 [test1],[test2] ,[test2] ,[test2] ,[test2] ,[test2] ,[test2],[test2] ,[test2] ,[test2] ,[test2] ,[test2] ,[test2],[test2] ,[test2] ,[test2] ,[test2] ,[test2] ,[test2] ,[test2] ,[test2] ,[test3] ,[test3] ,[test3],[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3],[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3],[test3],[test3],[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] ,[test3] FROM [test].[dbo].[table];
The error appears on the screen in MSSQL.
2. Run a query that is much shorter, but will still trigger the same query rewrite rule in the installed policy, for example:
SELECT TOP 1000 [test1],[test2],[test3] FROM [test].[dbo].[table];
No error appears.
Resolving The Problem
This behavior is a limitation of query rewrite functionality. There is no complete resolution, but workarounds are possible:
- Change query rewrite definition to make rewritten query as short as possible. For example use * instead of *** for masked result.
- If database user gets this error, break the query into smaller parts and re run.
- Increase the maximum network packet size of MSSQL. Note, this should only be changed after careful analysis by the database admin, there may be performance impact by changing it. Microsoft documentation (non IBM link) explains how to change this setting.
Was this topic helpful?
Document Information
Modified date:
30 July 2018
UID
ibm10718751