Execution alerts

Applies to

ApexSQL Complete

Summary

This article describes how to set and use the Execution alerts feature.

Description

This feature analyzes SQL code, that is about to be executed, against a defined set of rules with the ability to block query execution and produce a warning message, that provides the option to Proceed or Cancel, in which case the script will either be executed or the execution will be aborted, respectively

For example, if is attempted to execute a Delete statement, without a Where clause, like the statement below:

DELETE FROM Person.AddressType

The following warning message will appear:

The Execution alert feature initially ships out-of-the-box with five built-in alerts:

• Create statement on a Primary filegroup

  Triggers an alert on SQL code execution that creates objects on the Primary server filegroup

  For example, if the following code is executed:

  CREATE TABLE [Person].[AddressType] (
      [AddressTypeID] [int] IDENTITY(1, 1) NOT NULL
      ,[Name] [dbo].[Name] NOT NULL
      ,[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL
      ,[ModifiedDate] [datetime] NOT NULL
      ,CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED ([AddressTypeID] ASC) WITH (
          PAD_INDEX = OFF
          ,STATISTICS_NORECOMPUTE = OFF
          ,IGNORE_DUP_KEY = OFF
          ,ALLOW_ROW_LOCKS = ON
          ,ALLOW_PAGE_LOCKS = ON
          ) ON [PRIMARY]
      ) ON [PRIMARY]

  The following message will appear:

  

• Delete statement without a Where clause

  Triggers an alert on SQL code execution that contains a Delete statement without a Where clause.

• Drop table statement

  Triggers an alert on SQL code execution that contains a Drop statement.

  For example, if the following SQL code is executed:

  DROP TABLE Person.AddressType

  The following warning message will appear:

  

  The warning message also shows on which line (e.g. 5) in a query editor the SQL code contains the Drop statement.

• Update statement without a Where clause

  Triggers an alert on SQL code execution that contains an Update statement without a Where clause.

  The following warning message box will appear when execute an Update statement without a Where clause:

  

• Truncate table statement

  Triggers an alert on SQL code execution that contains a Truncate statement

  When executing a Truncate statement:

  TRUNCATE TABLE HumanResources.JobCandidate;

  The following warning message will appear:

  

  If there exists more than one SQL statement that can cause potential problems, all problematic SQL statements will be detected and displayed with a number of lines that indicates where that statement is located:

  

All these alerts are located under the Execution alerts tab of the Options window:

Additionally, to avoid performance problems or other issues, the option can be set on the maximum size of the SQL scripts the execution alerts will be run by entering a desired value in the Do not use alerts for script bigger than field:

By default, the size of the SQL script is set to 1MB (1024 KB)

The rules will be included in the executing process of the SQL code can also be selected:

Also, all the execution alerts can be deleted, edited or new execution alerts can be created.

Clicking on the Default alerts button, all changes made to the built-in alerts will be restored to default. This only applies to built-in alerts, while custom alerts will be intact.

Also, all deleted built-in alerts will be restored when click the Default alerts button.

To create a new alert, under the Execution alerts tab, click the New button:

This will open the New alert window:

On this window, a new rule name and conditions can be defined.

Rules are divided into 4 different components:

• Statement context
• Specific clause
• Data
• Custom text

Statement context

The statement context option defines the alert scope based on the type of statement e.g. create, drop, update, delete and/or truncate.

The image below shows the creation of an alert that blocks the execution of SQL code that contains a Drop statement:

Now, when execute the code below:

DROP TABLE Person.AddressType

The following message will appear:

Specific clause

The statement clause option offers to check for existence or lack specific clause (e.g. Where clause) in SQL statement that is being analyzed.

For example, if an alert is created on a Select statement like on the image below:

The execution alert message will appear for every block of SQL code that contains a Select statement without a Where clause. For example, if the following code is executed:

SELECT at.*FROM Person.AddressType at

The message below will appear:

Data

The Data option offers checking for existence or lack of a Temporary table in the SQL statement that is being analyzed.

For example, if the following SQL code is executed:

DELETE FROM #Colors

Data from the #Colors temporary table will be deleted.

To prevent this scenario, let’s create a new alert the that will prevent execution of the Delete statement without a Where clause even on a Temporary table. On the Execution alerts tab press the New button, this will open the New alert window. Enter the rule from the below image and press the OK button:

Now, when execute the above-mentioned SQL code the following warning message will appear:

Custom text

The custom text rule component is used for checking if a statement contains or doesn’t contain a defined text string.

For example, if the execution of a query contains an Inner join clause that should be prevented, choose the Custom text item in the Condition drop down box, then select the Contains item in Condition drop down box, and finally, in the Value field type, specify inner join:

Now, when the code that contains the Inner join clause is executed, for example:

SELECT a.*
FROM Person.Address a
INNER JOIN Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID

The following message will appear:

FAQs:

Q: Does the Execution alert feature analyze selected code in just the selected script or the whole script?

A: The Execution alerts feature work on both ways.

If nothing is selected in the query script then this feature analyzes a whole script that is about to be executed against a defined set of rules, but if is selected the part of a script than that selected code will be analyzed and if a rule violation is detected then the appropriate warning message will appear (e.g. deleting data without a where clause):

Q: Where is the information about the execution alert is stored?

A: By default, execution alert is stored in the following location:

<Drive>: \Users\<Current_user>\AppData\Local\ApexSQL\ApexSQLComplete\Alerts

Q: Can I use my saved execution alerts on a different machine?

A: Yes. The execution alerts can be used on multiple machines. This can be done by copying the content of the Alerts folder to the appropriate location on another machine where ApexSQL Complete is already installed.

Q: Can I edit the execution alert outside the host application (SQL Server Management Studio or Visual Studio)?

A: Yes. XML files that keep stored the information about the execution alerts can be edited.

Q: How can I disable the Execution alerts feature?

A: Under the Execution alerts tab of the Options window uncheck the check box next to the Name column for alerts:

Q: Can I transfer alerts from one machine and share it with others?

A: Using the Import/Export options in the Execution alerts tab, any and all alerts can be saved as a compressed file and used across the team: