3.20 RunSql

Use this Knowledge Script to run SQL statements or stored procedures. You can enter the SQL Server statements to be executed when you run this script, or you can load the statements from a script file and specify a full path to the file you want to use.

You can set maximum and minimum thresholds to be compared against the SQL Server statement’s primary output value--either the number of rows returned, or the value found in the column you specified. This script raises an event if the output value exceeds the maximum threshold or falls below the minimum threshold you set.

When constructing a SQL statement for use with this script, include the database name in the syntax.

For example:

select categoryid from Northwind.dbo.categories

This script might encounter problems if you employ the USE command to qualify queries.

For example:

use mydatabase select * from mytable.

Instead, specify select * from mydatabase.dbo.mytable to fully qualify queries.

AppManager does not provide a syntax-checking mechanism. Syntax checking is performed by the SQL Server on the monitored server when the job is run. If an error is detected, the SQL Server passes an error to the Knowledge Script.

Resource Object

SQL Server instance

Default Schedule

The default interval for this script is Every 5 minutes.

Setting Parameter Values

Set the following parameters as needed:

Description

How to Set It

General Settings

Job Failure Notification

Raise event if job fails unexpectedly?

Select Yes to raise an event if the SQLServer_RunSql job fails unexpectedly. The default is Yes.

Event severity when job fails unexpectedly

Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the job fails unexpectedly. The default is 5.

Raise event if SQL Server login fails?

Select Yes to raise an event if login to SQL Sever fails. The default is Yes.

Event severity when SQL Server login fails

Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the login to SQL server fails. The default is 15.

Additional Settings

 

Event Details

 

Event detail format

Select the format in which you want to display the event detail. You can select from HTML Table or Plain Text. The default is HTML Table.

Authentication

Select the authentication method that you want to use to access SQL Server. You can either select Windows Authentication or SQL Server Authentication. The default is Windows Authentication.

User name

Specify the Windows or SQL Server user name that you want to use to access SQL Server. You can specify multiple users separated by a comma. The default is none.

For more information on specifying user name, see Specifying the User Name in the Knowledge Script in the Management Guide.

For more information on specifying user name, see Section 2.7.3, Specifying the User Name in the Knowledge Script.

Monitoring

Timeout

Specify the number of seconds to wait while trying to establish a SQL connection before terminating the attempt and raising a timeout exception. The default is 30 seconds.

Number of retries

Specify the number of times the script should retry establishing a SQL connection before terminating the attempt. The default is 0 (no retry attempts).

SQL query or command source

Select one of the following options:.

  • Parameter: Select this option if you want to enter the T-SQL query as a single-line statement.

  • File: Select this option if you want to specify the file path that contains the list of SQL queries.

SQL query or command statement

If you have selected the Parameter option in SQL query or command source, enter a single-line statement here.

IMPORTANT:This parameter does not accept multi-line statements.

HINT:Unless you are entering very simple statements, you might find that typing them into this field is error-prone. To avoid errors, you can copy and paste statements, or use the Full path to SQL query or command file parameter.

Full path to SQL query or command file

If you have selected the File option in SQL query or command source, enter the complete file path.

For example, F:\NetIQ Corporation\Sample.txt, or \\<computer>\<dir>\Sample.txt.

NOTE:The T-SQL statements in the file must be separated by semi-colons.

For more information, see Loading SQL Server Statements From a Script File.

You cannot use a UNC path if the NetIQ AppManager Client Resource Monitor service is running as a system account.

Specify a description for the SQL query

Specify a description for the SQL query that you want to display in the event detail message. By default, the description is Detail result of query.

Save query results to a file?

Select Yes to save the query results to a file. By default, query results are not saved to a file.

Full path to results file

If you are saving query results to a file, enter the complete file path and filename. For example, F:\NetIQ Corporation\QueryResult, or \\<computer>\<dir>\QueryResult.

You cannot use a UNC path if the NetIQ Corporation AppManager Client Resource Monitor service is running as a system account.

Append to results file?

Select Yes to append the results of the query to the existing contents of the results file. If disabled, the results file is overwritten each time.

By default, the contents of the results file are appended each time a query or command is run.

Number of rows to write to results file

Specify the number of rows to write to the result file. The default is 10.

Include query results in event details?

Select Yes to include the results of your query or command in the event detail message.

By default, query results are included.

Number of rows to display in event details

Specify the number of rows to display in the event detail message.

The default is 10 rows.

NOTE:You can enter 0 to indicate all rows. However, the event detail message has a 32-KB limit.

Include query results in data details?

Select Yes to include the results of your query in the data detail message.

By default, query results are included.

Number of results to display in data details

Specify the number of rows to display in the data detail message.

The default is 10 rows.

NOTE:Enter 0 to indicate all rows. However, the event detail message has a 32-KB limit.

Event notification

Threshold type

Select whether you want to perform threshold checking on one of the following:

  • Number of rows returned: Perform threshold checkin on the number of rows returned by the command or query. If the number of rows exceeds the maximum threshold or falls below the minimum threshold, an event is raised.

  • Value in column number: Perform threshold checking on the value found in a column, specified by the number of the column. Column numbers start with 0 for the leftmost column. If the value in the specified column exceeds the maximum threshold or falls below the minimum threshold, an event is raised.

  • Value in column name: Perform threshold checking on the value found in a column, specified by the name of the column. If the value in the specified column exceeds the maximum threshold or falls below the minimum threshold, an event is raised.

Column number

If the threshold is comparing values found by column number, indicate the column number to use as the primary output value. The column you specify must contain numeric data. A value of 0 returns the first column from the SQL statements. Any other positive value returns the value for the first row of data from the specified column. If the specified column is not a numeric field, an error is raised and the Knowledge Script returns a -1 result. If you do not select by column number, you can select by column name. The default is 0.

NOTE:You should execute your query in SQL Query Analyzer to ensure that the proper rows and number of columns are returned. The leftmost column is column zero. Also note that if a query is doing a “Select * ...“ and if the table schema changes, the column numbering might change.

Named Column

If the threshold is comparing values found by column name, indicate the column name to use as the primary output value. The column you specify must contain numeric data. The value for the first row of data in the specified column is returned. If the specified column is not a numeric field, an error is raised and the Knowledge Script fails.

Raise event if maximum threshold is exceeded?

Select Yes to raise an event when the T-SQL statements run by this script return more rows than the threshold, or if the value in the specified column exceeds the threshold you set.

The default is Yes.

Event severity when maximum threshold exceeded

Set the severity level, from 1 to 40, to indicate the importance of an event in which the maximum threshold is exceeded.

The default is 5.

Threshold -- Maximum

Specify the maximum number of rows that can returned by the query or for the value found in the column. If the number of rows exceeds the threshold value, an event is raised. The default is 100.

Raise event if minimum threshold is not met?

Select Yes to raise an event when the T-SQL statements run by this script return fewer rows than the threshold, or if the value in the specified column falls below the threshold value you set.

The default is Yes.

Event severity when minimum threshold not met

Set the severity level, from 1 to 40, to indicate the importance of an event in which the minimum threshold is not met.

The default is 5.

Threshold -- Minimum

Specify the minimum number of rows that can be returned by the query or for the value found in the column. If the number of rows falls below the threshold value, an event is raised.

The default is 0.

Event detail options

Raise event for SQL Server informational messages?

Select Yes to raise an event when your query generates a Microsoft SQL Server informational message.

The text of the message is returned in the event detail message. This option is helpful when SQL Server returns the query result as an informational message rather than as data. For example, when using the dbcc checktable command.

By default, this script does not raise an event under these conditions.

Event severity for SQL Server informational messages

Set the severity level, from 1 to 40, to indicate the importance of an event in which a SQL Server information message is generated.

The default is 35.

Raise event if no rows are returned?

Select Yes to raise an event if no rows are returned.

The default is unselected.

Data Collection

Collect data for number of rows returned?

Select Yes to collect the number of rows returned by your query. If this parameter is set to Yes and the value returned is -1 or any negative number, an error occurred while attempting to collect data.

By default, this script does not collect data.

Custom data stream legend

Specify a custom legend for the data stream returned by this Knowledge Script.

Loading SQL Server Statements From a Script File

Because these SQL Server statements are passed through the ODBC driver, you do not need to enter the go statements normally required in the command line tool, osql. Example of a simple SQL Server script:

SELECT COUNT(*) FROM QBD.dbo.Event 
WHERE ParentEventId IS NOT NULL AND Status = 0;
SELECT EventID, JobID, MachineName, KPName, Severity, Occurrence, 
ModificationTime, EventMsg FROM QDB.dbo.Event 
WHERE ParentEventId IS NOT NULL AND Status = 0