3.1 ADODSNQuery

Use this Knowledge Script to query a Microsoft SQL Server using ActiveX Data Objects (ADO) and a system Data Source Name (DSN).

This script generates the following data streams:

Response time
- Overall response time. The information returned by this data stream is also saved with the data point, and can be viewed by double-clicking the data point in the Graph Pane or Chart Console.
- Response-time Breakdown. If enabled as separate parameters, up to three response-time breakdown data streams. These are individual data points for the different parts of the Knowledge Script transaction that are timed.
Availability. This data stream returns one of two values, depending on the data stream format you selected:
- 1 or 100 = transaction was successful
- 0 = transaction was not successful
The Availability data point is an indication of whether the transaction succeeded or failed.

This script raises an event whenever one of the following occurs:

A threshold that you have specified as an event parameter is exceeded.
The SQL-RT engine cannot be initialized. An initialization error is raised, but neither an Availability nor a Response Time data stream is generated.
The job transaction does not complete successfully. A transaction error is raised. Only an Availability data stream is generated, with a value of 0.

You can select where some of the possible events are displayed in the Operator Console TreeView or Control Center Console Server view. This event proxying feature is useful in Control Center Service Map views. It is not supported for jobs that are started in the Operator Web Console.

3.1.1 Resource Object

The SQL response time ADO client

3.1.2 Default Schedule

The default interval for this Knowledge Script is Every 15 minutes.

3.1.3 Windows NT Authentication

The ADOQuery and AdvancedADOQuery Knowledge Scripts support both SQL Server authentication and Windows NT authentication; however, the ADODSNQuery Knowledge Script is limited using the security settings in the DSN configuration because ADO cannot overwrite this kind of DSN setting.

Therefore, the job may not use the type of authentication you expected it to use. For example, say you set an invalid SQL username and/or password for the ADODSNQuery parameters, but the job still runs successfully. This can happen when the DSN is set to use Windows authentication, also called “integrated security.”. In such a case, if the Run As Username parameter has appropriate privileges on the SQL Server database, the OLEDB driver ignores the specific logon user and password supplied in the Knowledge Script parameters. The logon works because the Windows authentication configured in the DSN takes precedence over the SQL Server authentication specified in the Knowledge Script.

3.1.4 Setting Parameter Values

When running this script, use the security model that was set when you defined the DSN.

When using a DSN configured to use SQL authentication, use the SQL Logon Username and Password parameters. Or specify a valid Windows account for the Run As parameters.
When using a DSN defined with Windows NT authentication, leave the SQL Logon Username and Password parameters blank, and specify the valid Windows account for the Run As parameters.

Set the following parameters as needed:

Description	How to Set It
Availability
Collect data for availability?	Select Yes to collect data for graphs and reports. If enabled, data collection returns: 1 or 100 -- Transaction completed successfully 0 -- Transaction did not complete successfully Time taken to execute the query (in seconds) The default is Yes.
Data stream format	Select a format for the Availability data stream. You can select a 0 (“not available”) or 100 (“available”) format. The default value is 0-100.
Raise event if transaction fails?	Select Yes to raise an event when the server cannot be contacted. The default is Yes.
Event severity when transaction fails	Set the event severity level, from 1 to 40, to indicate the importance of an event in which the server cannot be contacted. The default is 5.
Response Time
Collect data for response time?	Select Yes to collect response-time data for graphs and reports. The default is Yes. If you enable data collection, you also have the option to see a breakdown in the response times for the component parts of the query, such as the time taken to connect to the SQL Server.
Threshold -- Maximum response time (seconds)	Specify the maximum response time in seconds. When response time exceeds this value, an event is raised. The default is 15 seconds.
Raise event if threshold is exceeded?	Select Yes to raise an event when the threshold is exceeded. The event message contains a breakdown of the total response time. The default is Yes.
Event severity when threshold is exceeded	Set the event severity level, from 1 to 40, to indicate the importance of an event in which response time exceeds the threshold. The default is 15.
Response Time Breakdown
Collect data for connecting to SQL Server?	Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to establish a connection to the SQL Server. The default is unselected.
Collect data for executing SQL statement?	Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to execute the SQL statement. The default is unselected.
Collect data for fetching data?	Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to perform a fetch of the query data. The default is unselected.
Target computer	Provide the computer name and instance of the SQL Server. This is an optional field, and is used to enable retrieval of data streams by AppManager Analysis Center v2.0 and later. If specified, it will also be used in place of the DSN in the data stream legend. If you set the Event on parameter, the Target computer parameter lets you select the server where the event will appear in your console. Enter the name of the server, or click Browse [...] to select from a list of available servers. The server you select must already be in the TreeView.
Data source (DSN)	Provide the Data Source DSN used for the connection.
Attributes	Specify the ADODB::Connection attributes as follows: NONE (the default). No attributes. ABORTRETAINING: Performs retaining aborts; i.e., calls RollbackTrans and automatically starts a new transaction. Not supported by all providers. COMMITRETAINING: Performs retaining commits; i.e., calls CommitTrans and automatically starts a new transaction. Not supported by all providers. ABORTCOMITRETAINING: A combination of ABORTRETAINING and COMMITRETAINING.
Cursor location	Specify the location of the cursor service: CLIENT: Uses client-side cursors supplied by a local cursor library. (Local services may allow many features not allowed by driver-supplied cursors; using this setting may provide some advantage in enabling features.) SERVER: Uses data provider- or driver-supplied cursors. (These cursors may be flexible and allow for additional sensitivity to changes made to the data source by others.) This is the default.
SQL statement	Provide a SQL statement (1024-character maximum) that is compatible with the provider. Calling stored procedures is also supported. The format is execute stored_procedure.
Number of rows per fetch	Specify any positive integer or -1. Use an appropriate value according to the size of the result and of a single row. The default value is 1. If the SQL statement is a select, the engine uses the GetRows() method to retrieve the data, so you can fetch thousands or records at once. This could mean a huge performance improvement in production. A value of -1 attempts to retrieve all rows on a single fetch. Although this may show interesting results on a small database, it can easily become catastrophic if the result is large and the client machine has limited memory. You should change this value only if the GetRows () method is used in production with a value different than 1.
Event on	Select the TreeView location where events should be displayed. Select either: Agent (the client computer in the response-time tests). This is the default. Server (the AppManager server being tested--see the Target computer parameter, above) Both. The event will be shown in two locations in the TreeView. Notes This setting does not apply to events related to the Knowledge Script itself, such as Knowledge Script failure or initialization problems. Such events are always displayed on the computer where the job ran. You must select Agent when starting jobs in the Operator Web Console. If you select Server, no events are generated. If you select Both, an event is only shown on the agent.
SQL Logon
Username	Set this value appropriately based upon your DSN settings.
Password	Set this value appropriately based upon your DSN settings.
Run As
Username	Provide the username associated with a specific user who has the required permissions to run this application. Required. Interactive User is a possible value. Leave the Password and Domain parameters blank if you specify "Interactive User". Interactive User requires a user to be physically logged into the computer for the test to run. You might want to do this in environments where a firewall is preventing access to an Active Directory domain controller, or where the test computer is part of a workgroup and not part of a domain. With this feature, the user is not validated, so the test can proceed despite the lack of access to the domain.
Password	Specify the password associated with this user that is required to log on to the network and run the application.
Domain	Specify the domain associated with this user that is the domain name you are logging onto. Required.
Administrators group on managed client	Provide the name of the Administrators Group on the managed client. Typically, this name is “Administrators”, except on some foreign language operating systems. The default is “Administrators”.
Timeouts
Command timeout	Specify the number of seconds to wait while establishing a connection before terminating the event and raising an event. The default is 30 seconds.
Connection timeout	Specify the number of seconds to wait while executing a command before terminating the attempt and raising an event. The default is 15 seconds.