Command rules are designed to enable the execution of user defined SQL statements in the target database. There can be any number of Command rules applied to a database.

Command rules are created using the New Command rule dialog box. There are two methods of operation: Script mode which runs the commands individually as a script or GO block mode which runs the commands as a block. The primary difference between the two modes is how the error exceptions are handled. Script mode assumes assumes each statement is terminated with a semi-colon character. Each statement will be presented individually to the SQL Server. If an error occurs, it is possible to proceed to subsequent statements if the error is listed as permitted. In the second mode, the T-SQL statements are delimited with a GO command. All statements in each GO block are presented together to the SQL Server. If an error occurs in one of the statements in a GO block then the subsequent statements within the same block may not execute - even if the error is permitted. GO blocks are primarily used when creating procedures.

-- example of GO block usage
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL
     DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
     SELECT LastName, FirstName, JobTitle, Department
     FROM HumanResources.vEmployeeDepartment;
GO

GO block mode is useful if the logic requirements are too complex for simple SQL statements (create procedure statements for example). Command rule Script mode is simply a collection of SQL statements executed sequentially and the effect is similar to executing a T-SQL script in a SQL session.

Command rules cannot report the number of rows on which they have operated back to the Data Masker software. Consequently the Rows Processed statistics visible on the Rule Statistics tab will always be blank for Command rules.

Creating Command Rules

Command rules are created by launching the New Command rule form using the New Rule button located on the bottom of the Rules in Set tab.

How to Create a New Command rule

Multiple SQL statements in one script

It is possible to implement an unlimited number of SQL statements in a Command rule. For example, in order to clear down some tables and re-populate them with some pre-prepared dummy data, the SQL statements below could be entered:

truncate table dm_expenses;
truncate table dm_expense_items;
insert into dm_expenses
    (select * from dummy_expenses);
insert into dm_expense_items
    (select * from dummy_expense_items);

The only drawback to including multiple SQL statements in one rule is that if errors occur it can be harder to relate the messages returned from the SQL Server back to the SQL statement that caused the problem. If multiple command rules are used to implement a series of statements remember to implement rule blocks or dependencies in order to control the exact sequence in which the commands execute.

The Ignore Listed Errors Option

Normally, the Data Masker will stop processing the rule and the masking set if the SQL Server reports any errors. These errors are reported using the normal SQL Server error codes and messages. Sometimes, when running Command rules, it is desirable to be able to ignore reported errors and continue processing. An example of this is a statement of the form:

drop table TempTable;

If the table does not exist, the returning 3701 Cannot drop the table error would completely halt the run of the masking set. However, in this case, the error is of no consequence. Enabling the Ignore Listed Errors option and specifying either the error code 3701 or part of the error message (or both) in the errors panel will cause those errors to be ignored for every statement in the Command rule and allow subsequent statements to be processed.

It is important to realize that the Ignore Listed Errors option applies to all SQL statements in the Command rule. If it is not appropriate to ignore the specified error code for all statements in the rule then it is advisable to split the statements out into separate Command rules.