sqlserver custom checkpoint returns 0 rows with 'use database' argument

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The following custom checking point was added to the sqlserver probe and noticed that 0 rows are returned when running sqlserver probe -> Query ->  Test option even though the query works fine with SQL Server Management Studio

use <databasename>
select count(*) as count from <tablename>

sqlserver_monitor.log entries

sqlserver: (doQuery) Profile: test query profile/test query checkpoint, doQuery test query starting
sqlserver: doQuery qeury text use <databasename>
select count(*) as count from <tablename>
sqlserver: (doQuery) Retry : 0 - Query Execution Failed - State : 0 - Profile : test query profile

Environment

sqlserver 5.61+

Resolution

This behavior is expected when the 'use <database>' parameter is included in custom query and the sqlserver probe connection is configured with Windows Authentication with TLS option enabled. 

See sqlserver probe Known Issues and Workarounds

• When using a custom checkpoint with Windows Authentication and a TLS-enabled connection, the 
  USE <databasename>; <Query> syntax does not work because Microsoft SQL Server treats it as multiple batches, causing the user context to be re-evaluated. To resolve this, create a stored procedure for the custom query and execute it.