Managing TSQL Monitoring Statements

Gain full control over your SQL Server monitoring with the Nodinite Database Monitoring Agent. This page shows you how to leverage custom TSQL scripts for proactive monitoring, instant alerts, and self-service management—empowering both integration experts and business users.

✅ Monitor SQL Server health and business data using your own TSQL logic
✅ Instantly receive alerts and automate remediation with Remote Actions
✅ Empower self-service for business users to execute pre-defined scripts
✅ Centralize monitoring and management for all your SQL Server resources

Animated walkthrough: the statement 'Contoso: Orders awaiting confirmation' is Error, and its THROW message is the Log Text. Details, then Show Action Scripts: Execute 'Show the orders awaiting confirmation', confirm, and read the seven orders in the result.

Automate your database monitoring and get alerts using your custom coded TSQL statements! Gain actionable insights and perform administrative tasks by executing pre-defined TSQL scripts—all from a single platform.

The Nodinite Database Monitoring Agent provides two major features for SQL Server:

1. Monitoring – Get alerts from logic in custom-built TSQL statements
2. Management – Allow end-users to execute pre-defined TSQL statements from one or more TSQL Script collections

Here's a visual overview of how monitoring and management features are organized in Nodinite.

To use the Monitoring feature, provide either Stored Procedures or Text/AdHoc TSQL statements. Implement your business logic using the examples on this page.

• Text/AdHoc
• Stored Procedures

Each SQL Statements entry in the Nodinite Database Monitoring Agent manifests as one Resource to manage. Assign these Resources to one or more Monitor Views to trigger alerts according to the evaluated monitor state. Use the Nodinite Alarm Plugins to push alerts.

In the Monitor Views, you can group the specified TSQL Statements Resources by the SQL Statements Category.

Here's an example of a Monitor View with filter by the 'SQL Statements' category.

• Your custom-built SQL Statements for Microsoft SQL Server appear in Nodinite as Resources, where the name of the SQL Statement configuration becomes the Resource name.

Each entry manifests as one Resource to monitor.

What are the key features for Monitoring SQL Statements?

• State Evaluation – Monitor and evaluate the state based on your custom TSQL logic. See the TSQL/Stored procedure code examples further down in this guide.
• Actions – Support for the execution of Remote Actions

How do SQL Statements evaluate?

The following monitoring states exist:

Tip

You can reconfigure the evaluated state for a Resource using the Expected State feature.

A statement raises a state with THROW: state 1 is Error, state 2 is Warning, and the exception message becomes the Log Text, for example ERROR: 7 orders have waited more than an hour for their confirmation (limit 3). A statement that ends without an exception is OK. See the Text Example.

How do I add my custom coded TSQL Statements?

The Database Monitoring Agent automatically monitors each configured SQL Statement. The Nodinite Administrator can add new and modify existing SQL Statements using the Remote Configuration dialogue for the Database Monitoring Agent

Global Configuration

In the Remote Configuration, open the SQL Servers tab and expand the SQL Server Instance accordion. Each instance has the tabs General, Advanced, Backups, Jobs, Size checks, SSIS, and SQL Statements. Click the SQL Statements tab.

The SQL Statements tab of an instance: one accordion per SQL Statement; a crossed-out entry is disabled.

The Nodinite Administrator can add new SQL Statements from the Remote Configuration dialogue, detailed in Add or manage a Monitoring Agent.

From the SQL Statements tab in the SQL Connection configuration, click the Add button.

You can add any number of SQL Statements. Make sure that each one executes quickly, or you may end up hogging the agent, resulting in timeouts.

Example to add a new configuration for SQL Statements using the Add button.

Then, expand the accordion.

The following tabs exist:

• General tab
• Monitoring Statement tab
• User Action Scripts tab
• Authentication tab

Note

Display names must be unique across all SQL Server, Azure SQL, and PostgreSQL entries of the agent; the agent refuses to save a name that two database types use (Duplicate configuration names are not allowed across all database types).

General tab

To manage the essential configuration, click on the accordion to expand the details:

Here's an example of essential settings for a SQL Statements configuration.

You can manage the following properties:

• Enabled – Set if monitoring for this configuration is enabled or not.
• Display Name – Manage the user-friendly Name. This setting must be unique for each SQL Statement configuration.
• Description

Monitoring Statement tab

To manage the SQL Statement to use, click on the Monitoring Statement tab.

Example configuration from the Monitoring Statement tab.

• Command Type – The Type of 'SQL Command', select one of the following:
  • Text – For AdHoc TSQL Statements
  • Stored Procedure – For existing parameterless stored procedures
• Command Text
• Execute Command – The SQL command(s) to execute

Important

This feature can execute custom TSQL with significant impact if it is misconfigured.

Use the centralized security guide for least privilege, dedicated connection strings, impersonation boundaries, and credential isolation:

Secure Custom Database Scripts and Actions

Stored Procedure

Execute one or more stored procedures in the SQL Server database provided by the connection string. For example, enter [YourCustomProcedure] or the full path.

[Database].[dbo].[YourCustomProcedure]

Here's an example of stored procedure call using a full path.

Info

If you are using a Stored Procedure, you MUST enter the name with just one (1) line of code in the text field; Otherwise the evaluation will fail.

User Action Scripts tab

Add entries in the 'User Action Scripts' tab to allow your end-users to execute scripts on demand. You do not need to have anything but a comment in the Monitoring Statement tab.

Example of the User Action Scripts tab for on-demand script execution.

Each entry is one Resource. Each entry may have any number of scripts.

Click the Add button to add a script.

Then, expand the accordion.

You can then fill out the following properties:

• Enabled – When checked, this SQL Action Script is visible in the end-user list of executable scripts
• Display Name – The user-friendly name of this action script
• Description – User-friendly description for this SQL Action Script
• Command Type – The type of SQL command
  • Text
  • Stored Procedure
• Execute Command – The SQL command to execute
  
  Example of a script entry for on-demand execution.

Authentication

^{New 6.2.0.0}
Each SQL Statement runs with its own connection string, and can impersonate a domain user.

Example of the Authentication tab for script execution.

• Connection String – The connection string for this SQL Statement. The 7.11.3 agent requires it: it refuses to save a SQL Statement without one (requires a connection string).
• Use Impersonation – Run the statement as the domain user below. Required when the connection string uses Integrated Security; otherwise the agent refuses to save (uses Integrated Security in its connection string. Please enable 'Use Impersonation'...).
• Domain User – The name of the Windows user account. Format: DOMAIN\User
• Password – The password for the provided domain user

Important

CRITICAL: DO NOT USE THE AGENT'S SERVICE ACCOUNT

The credentials provided in the Authentication tab MUST NOT be the same account that runs the Database Monitoring Agent service. The agent runs with elevated privileges, and reusing those credentials defeats the isolation model.

Prohibited: Using the agent service account (for example DOMAIN\NodiniteAgentService) for SQL Statement execution.
✅ Required: Use dedicated SQL credentials with least privilege.

For the complete baseline and examples, see Secure Custom Database Scripts and Actions.

Save

You must click 'Save' or 'Save and close' for changes to persist. The new settings/thresholds are evaluated on the next synchronization.

Here's an example of the Save options.

Note

Depending on the synchronization interval set for the agent, there might be a delay before the Nodinite Web Client reflects the change. You can choose to force the agent to synchronize from the configuration of the Monitoring Agents.

Save and close saves and closes the dialogue.
Close closes the dialogue without saving any changes.

Actions

The following Remote Actions are available in the Control Center part of the Action menu:

• Details
• Show Action Scripts

Example of available Remote Actions for SQL Statements.

Details

Click Details in the Action menu. The Details modal shows the Server, Database, and Application of the SQL Statement and an editable Description; it does not run the statement or show a result.

Example of the details modal.

Show Action Scripts

You can execute pre-defined TSQL scripts and immediately see the result. The Nodinite Administrator manages the collection of scripts in the User Action Scripts tab.

Click Show Action Scripts in the Action menu. The Action Scripts dialog lists the scripts of this SQL Statement, each with an Execute button.

Example of the Action Scripts dialog.

Click Execute on a script and confirm the intent: Execute action? Are you sure you want to execute the SQL script '…'?

Confirmation dialog for script execution.

The result shows under Output from script name, with a Table and a Raw tab, a timestamp, the row count, and the duration.

Example with the result from executing your custom TSQL Script on demand.

Examples

There are two ways to provide the outcome (Monitoring state of the Resource) based on the logic in your custom-built SQL Statements:

Use a supported implementation according to the use of either Text/AdHoc or Stored Procedures.

Internally, the Nodinite Database Monitoring Agent executes the Execute Scalar method. Nodinite picks up data from the first column from the first returned row. The extracted value is set as the Log Text unless you raised an exception.

Important

Ensure your logic does not perform any processing or have side effects. We recommend that all TSQL commands execute within a second or so. This means you may need to adjust the background process and involve SQL Jobs (or other scheduled background tasks) to perform processing and then the logic here merely gets the result.

1. Select + return code

The LogText presented for the SQL Statement configuration (The Resource) comes from your last SELECT statement (1st row, 1st column). If you do not select anything, the Log Text is empty.

2. Select + throw (raiserror)

The LogText presented for the SQL Statement configuration (The Resource) comes from your last SELECT statement (1st row, 1st column) or from the Exception. If you do not select anything, the Log Text is empty.

Text Example

Example of using the SQL Statement with Command Type set to Text. This example counts the rows in the actual table. Depending on your logic, different states can be returned.

DECLARE @rowCount INT, 
        @log VARCHAR(256), 
        @errorLimit INT = 3, 
        @warningLimit INT = 1  
  
SELECT @rowCount = COUNT(1) FROM [ConnectionFailures]  
            
IF (@rowCount > @errorLimit)  
   BEGIN  
      SET @log = 'ERROR Number of connection failures exceeded ' + CAST (@errorLimit AS VARCHAR(18));  
      THROW 51000, @log, 1;  
   END  
ELSE IF (@rowCount > @warningLimit)  
   BEGIN  
      SET @log = 'WARNING number of connection failures exceeded ' + CAST (@warningLimit AS VARCHAR(18));  
      THROW 51000, @log, 2;  
   END  
ELSE  
   BEGIN  
      SELECT 'OK'  
   END  

Note

THROW is available in SQL Server 2012 and later. In SQL Server 2008 R2, use RAISERROR (@log, 16, 1); RAISERROR(Message, Severity, State). The Severity should be set to 16. Such a high value is required to stop the execution of the query.

Stored Procedure example

Example of using the SQL Statement with Command Type set to Stored Procedure

[SP_CheckConnectionFailures]

Example of the Stored Procedure (existing in the actual database):

USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[SP_CheckConnectionFailures] 
AS

BEGIN
   SET NOCOUNT ON;
            
   DECLARE  @rowCount INT,
            @errorLimit INT = 3,
            @warningLimit INT = 1,
            @ret INT = 0,
            @log VARCHAR(256)
            
  SELECT @rowCount = COUNT(1) from [dbo].[ConnectionFailures]
            
  IF (@rowCount > @errorLimit)
     BEGIN
        SET @log = 'ERROR: The number of connection failures was exceeded: ' + CAST (@errorLimit AS VARCHAR(18));  
        SELECT @log
        SET @ret = -10
     END
  ELSE IF (@rowCount> @warningLimit)
     BEGIN
        SET @log = 'WARNING: The number of connection failures was exceeded: ' + CAST (@warningLimit AS VARCHAR(18));  
        SELECT @log
        SET @ret = 10
     END
  ELSE
     BEGIN
        SELECT 'OK'
     END
  RETURN @ret

END -- Procedure

Note

There is currently no way to provide arguments to the Stored Procedures. Use Command Type set to Text and pass arguments to the Stored Procedure from your Text/AdHoc TSQL code.

Next Steps

• Add or manage Monitor View

Related Topics

• Azure SQL Statements
• PostgreSQL SQL Statements
• Database Monitoring Agent
• SQL Categories
• Resources
• Monitoring Agents
• Monitor Views
• PowerShell Monitoring - Similar monitoring approach using custom PowerShell scripts
• Secure Custom Database Scripts and Actions - Centralized guidance for secure SQL statement and action-script execution