Environment & System variables – Embedding Parameters in SQL Code
Combine Variables – Environment and System Variables
Combine Variables is a collective name for Environment Variables and System Variables, which are two types of variables that can be embedded inside your SQL code. Variables can be embedded in scripts that are part of a code package, or in scripts that are executed directly from the editor, against a single DB from a connected window or against a Container. Environment Variables are also supported in the Query used by a Dynamic Container. When Combine executes code that includes Environment or System Variables, Combine replaces the value of the variables in run-time before code is deployed.
Environment Variables are user-defined parameters and are supported in package scripts, individual scripts in the editor, as well as Queries used by Dynamic Containers. If Environment Variables are found in your SQL code, then Combine will prompt you to validate and update the value of these parameters before code is executed. The syntax for Environment Variables is <$VariableName$> (for example, <$MyTableName$>). For additional information please refer to Introduction to Environment Variables.
System Variables are fixed and built-in (i.e., non-user-defined) variables that can be embedded in code. System Variables are supported in package scripts and individual scripts, however are not supported in the Query used by Dynamic Containers. When the SQL code contains System Variables, Combine will not prompt you to enter their value since the value of these parameters is automatically replaced by the application in run-time. The syntax for System Variables is <%VariableName%> (for example, <%Execution_DatabaseName%>). For additional information please refer to the System Variables section.
Note: The term
Introduction to Environment Variables
Environment Variables are user-defined parameters that can be embedded in SQL code. These variables are specific to each environment (and can vary between different environments). When code that uses environment variables is executed, Combine will prompt you to validate or update the value of each variable. Then, Combine will replace each Environment Variable with the appropriate value in the code and proceed to execute the code against the appropriate target databases. Environment Variables are supported in SQL scripts in code packages, in individual (i.e., non-package) scripts in the editor, and in the Query used by Dynamic Containers.
Example: A stored procedure is created in the Development environment and selects from a table called “utbMyTable”. In the Development environment, the owner of the table is “dbo”, however the table owner in the Production environment is “User1”. Using Environment Variables, you can write a single script to create the stored procedure in Development and in Production, without modifying the code before it is executed: First, define <$TableOwner$> as an Environment Variable in the Development and Production environments in the Container Manager. Then, assign the value “dbo” to this variable under the Development environment and “User1” under the Production Environment. Next, replace the reference to the table owner in your SQL script, for example:
— Replace the code:
1 2 3 4 5 | |
— With this code:
1 2 3 4 5 | |
Finally, when the code is executed against the Development environment, Combine will automatically replace the Environment Variable <$TableOwner$> with “dbo”. Similarly, when the code is executed against the Production environment, this value will be replaced with “User1” before it is deployed.
Environment variables can be used when running a script against a single database (in which case the assigned values are those under the Active Environment), when running a script against multiple databases, or when running a code package. In the last two cases, variable values are those defined under each environment, and Combine also allows you to validate and update the values of all Environment Variables before any code is executed against target databases (see Changing the Value of Variables in Run-Time for more information).
Note: Although the example above shows the use of a single Environment Variable in a script, each script can contain several variables. Furthermore, for code packages, you can use Environment Variables is all package scripts as needed for your code release.
Creating New Environment Variables
Environment Variables can be created in the Edit Environment Variables dialog. To do so, choose one of the following options as illustrated in the image below. After variables are created, they can be referenced in SQL code using the format <$VariableName$>, as demonstrated later in this section.
1. In the Container Manager, select your environment and choose Edit Environment Variables in the right-click menu.
2. Select your environment in the Container Manager and press F4 to view the Properties window. Then, in the Properties window, expand the Variables node and click the Browse button.
In order to create a new Environment Variable, under Add/Update Variable, first enter the name of the new variable. Then, enter the variable value and click the Add button, as demonstrated in Fig. 102.2.
After you press the Add button, the new variable will appear in the list of variables with a pencil icon next to it, as in Fig. 102.3. Once you are done adding variables, press OK to complete the dialog. The new variables will then be available in the Properties window for the environment you selected earlier.
After the variables are created, they can now be referenced in the code. To use them, the format <$VariableName$> must be used. For example, Fig. 102.4 shows SQL code that uses the LinkedServerName and TableOwner environment variables.
Another way to create Environment Variables is by copying variables that were previously defined for other environments. Please refer to the section Copying Environment Variables from Another Environment for additional information.
Changing the Value of Environment Variables in the Container Manager
The value of Environment Variables can be updated in the Container Manager, or immediately before code that contains variables is executed in run-time: Changes made in the Container Manager are saved in the Properties of each Environment, whereas changes made before execution are not saved and are only used for execution (see Changing the Value of Variables in Run-Time to learn more about updating values before execution).
To change the value of Environment Variables in the Container Manager, choose one of the following options as illustrated in Fig. 102.5 below:
1. In the Container Manager, select your environment and choose Edit Environment Variables in the right-click menu.
2. Select your environment in the Container Manager and press F4 to view the Properties window. In the Properties window, expand the Variables node and click Browse.
In the Edit Environment Variables dialog, either double-click a variable you wish to change, or right-click it and select the Modify option from the context menu.
Then, under Add/Update Variable, type the new value and press the Update button. When done, press OK to complete the dialog.
Changing the Value of Environment Variables in Run-Time
The value of Environment Variables can be changed and updated in the Container Manager, or immediately before code that contains variables is executed in run-time: Changes made in the Container Manager are saved in the Properties of each Environment (see Changing the Value of Variables in the Container Manager for info), whereas changes made before code is executed are not saved and are only used for execution.
Note: The value of variables can be changed in run-time when running a script (or query) against a Container in the editor, or when deploying code packages. In other words, if you are running code in a connected editor window (i.e., against a single database), you cannot assign values to variables in run-time yet you can still change values after previewing the script code.
The following examples demonstrate how to change variables value in run-time. Here, assume that two Environment Variables are defined for the Development environment in the Container Manager, namely TableOwner and LinkedServerName. These variables are used in the following script, which will be executed against the Web Databases container (under the Development environment).
After pressing F5 to run the script against the Web Databases container, Combine prompts the following dialog, which allows you to set the value of your variables. To update a value, select the variable in the drop-down or double-click the variable entry in the dialog, enter the new value, and then press the Update button. When done, press OK to continue with the execution.
The status of all the variables in the example above (see Fig. 102.8) is Valid. In other words, all variables were previously defined for the Environment against which the code is executed. However, in some cases, the status of variables may be invalid due to one of two reasons: (i) The variable is not defined for the Environment; (ii) No value is associated with the variable in the Container Manager. The following example demonstrates invalid Environment Variables.
As before, assume that two variables are defined under the Development environment, namely TableOwner and LinkedServerName. Next, the following code that contains an undefined variable called MyUndefinedVariable is executed.
When the code is executed against a Container or as part of a code package, Combine prompts the following dialog. Here, the undefined variable is marked as Invalid. In this dialog, you can update the value of the invalid (and other valid) variables, and then continue with the execution.
Copy Environment Variables from Another Environment
After you define Environment Variables for one environment, you can create those variables for different environments by copying variables. To so do, select the Environment that does not yet contain the desired variables and start the Edit Environment Variables dialog using one of the two techniques below:
1. In the Container Manager, select your environment and choose Edit Environment Variables in the right-click menu.
2. Select your environment in the Container Manager and press F4 to view the Properties window. Then, in the Properties window, expand the Variables node and click the Browse button.
In the Edit Environment Variables dialog, click More and select Copy Variables, or alternatively right-click the grid and select the Copy Variables option.
Next, Combine will prompt the dialog below. Here, select the environment that contains the variables you wish to copy and edit their values as needed. When done, press OK in the Edit Environment Variables dialog to save your changes.
Using Environment Variables in SQL Code
After Environment Variables are created, they can be referenced in SQL code. To use them, you must follow the format <$VariableName$> in the SQL code. For example, the figure below shows code that uses the LinkedServerName and TableOwner variables. Please refer to the section Introduction to Environment Variables for more information.
Preview Code That Uses Variables
After a SQL script that uses Environment Variables or System Variables is opened in the editor, you can preview the script with all variable values. To do so, open the script in the editor, right-click in the editor window, and select the option Combine Variables → Preview Script. A new window will then be opened where all variables in the script are replaced with the appropriate values.
Notes for Environment Variables:
1. If an editor window is connected to a database, Combine replaces the values of Environment Variables with the values specified for the Active Environment.
2. If an editor window is not connected to a database and no Container is selected for code execution in the Container drop-down (above the editor window), then as before, Combine replaces the value of Environment Variables with the values specified for the Active Environment.
3. If an editor window is not connected and a Container is selected for script execution, then Combine replaces the variables with the values specified for the Environment that contains the selected Container in the Container Manager.
Example: The following script contains two Environment Variables that are defined for the environment, namely LinkedServerName and TableOwner. This script also includes a variable called MyUndefinedVariable that is not defined for the environment. After selecting the Preview option (as demonstrated in the image above), the following window is opened in Combine. Here, Combine replaced <$LinkedServerName$> and <$TableOwner$> with the values specified for the environment, MyLinkedServer and dbo, respectively. Since the variable MyUndefinedVariable does not exist for this environment, it is therefore not replaced with a variable value.
Notes for System Variables:
System Variables are replaced with their values in runtime, therefore the Preview functionality is only supported when working with connected windows in the editor. In other words, using the Preview feature with windows that are not connected to a single database will not show the values of System Variables.
System Variables
System Variables allow you to embed a set of fixed (i.e., non-user defined) parameters and values in SQL scripts and code. System Variables can be embedded in SQL scripts that are part of a code package, or in individual (i.e., non-package scripts) that are executed directly from the editor, either against a single database through a connected window or against a Container.
There are several options which controls System Variables processing:
Enable System Variables
Enables/disables System Variables handling. If it is disbaled then System Variables aren’t processed at all. It may be helpful for the case when System Variables aren’t used but scripts contain strings which can be mistakenly treated as System Variables.
Prompt for invalid System Variables
This setting controls how Combine will behave in case if it find some invalid System Variable names. If it is set to “Prompt On Error” then Combine brings a dialog with list of invalid System Variables and let user to decide what to do — continue package execution or cancel. Two other possible values say Combine to proceed with invalid System Variables the corresponding way without user interaction.
Note: Invalid System Variables are ones that are not supported by the application (please see the full list of available System Vvariables). If you embed invalid System Variables in your code (for example, <%MyVar%>), then Combine will behave according to “Prompt for invalid System Variables” setting described above. This is the case when executing packages from the application or when executing scripts in the editor. CpaExec command line utility has an option to ignore invalid system variables.
System Variable List
The following System Variables are currently supported in Combine (note that the syntax of System Variables is <%VariableName%>):
<%Execution_DatabaseName%> – The name of the target database as it was obtained by running SELECT DB_NAME(). If this variable is included in the SQL code, then Combine executes SELECT DB_NAME() before code is deployed on each target database and then replaces the variable name with the result of this SELECT statement. For example, if code is executed against a database called MyDB, then the value of this System Variable will be replaced with the string MyDB in runtime.
Note: The <%Execution_DatabaseName%> is the name of the target database for the entire script. If a SQL script uses the USE clause to change databases in runtime, then the value of <%Execution_DatabaseName%> is the name of the original target database and not the one used after the USE clause. For example, if the following script is run against the master database
1 2 3 4 5 | |
then Combine will replace the variable value with the string master, since master is the target database for the script.
<%Execution_InstanceName%> – The name of the SQL Server instance that holds the target database where code is executed, as it was obtained by running SELECT SERVERPROPERTY(‘ServerName’) and by parsing the string after the forward slash if it exists (e.g., when the server name is MyServer\Instance1 then the instance name is Instance1). If this variable is included in the SQL code, then Combine executes SELECT SERVERPROPERTY(‘ServerName’) before the code is deployed and then replaces the variable name with the parsed instance name. For default (i.e., non-named) SQL Server instances, the value of this variable is an empty string.
<%Execution_ServerName%> – The name of the SQL Server that contains the target database where the code is executed, as it was obtained by running SELECT SERVERPROPERTY(‘ServerName’) and by parsing the string before the slash if it exists (e.g., when the result of the SELECT statement is either MyServer\Instance1 or MyServer, then the server name is MyServer). If this variable is included in the SQL code, then Combine executes SELECT SERVERPROPERTY(‘ServerName’) before the code is deployed and then replaces the variable name with the server name in runtime.
<%Execution_ServerAndInstanceName%> – The name of the SQL Server together with the instance name. For example, if code is executed against a database that belongs to a SQL Server instance called MyServer\Inst1 then the System Variable will be replaced with MyServer\Inst1 in runtime. For default (i.e., non-named) instances, the value of this System Variable is identical to the value of the System Variable <%Execution_ServerName%>.
<%Container_DatabaseName%> – The name of the database where SQL code is executed, as it was defined in the Container Manager. For example, if code is executed against a Container that points to three databases (on any number of servers) named DB1, DB2 and DB3, then the value of the System Variable will be replaced in runtime with the appropriate database name for each database. Note that this value may be different than the value of the corresponding System Variable described above <%Execution_DatabaseName%> since the latter is retrieved in run-time by running SELECT DB_NAME() against each target database whereas the System Variable <%Container_DatabaseName%> is the database name as it was defined in the Container Manager.
<%Container_InstanceName%> – The name of the SQL Server instance that contains a target database as it was defined in the Container Manager. For example, if you deploy code that embeds this System Variable against a Container, and the Container has a database that resides on a named instance and the instance name is defined in the Container Manager, then the value of this System Variable will be replaced with the instance name you defined in the Container Manager. However, if you define a Container that points to a database on a named instance using aliases or by specifying the port of the target instance (e.g., by using Dynamic Containers), then the value of this System Variables will be replaced with an empty string. Note that the difference between this System Variable and <%Execution_InstanceName%> is that the latter is resolved in runtime by querying the target database for the instance name on which it resides, whereas <%Container_InstanceName%> is simply taken from the settings in the Container Manager without resolving the actual instance name.
<%Container_ServerName%> – The name of the server that contains the target database on which code is executed, as it is defined in the Container Manager. The value of this System Variable does not include the instance name. Furthermore, if you define servers in the Container Manager using aliases, then the value of the System Variable will be replaced with the alias name and not the actual server name, as is the case with <%Execution_ServerName%>, since the latter is resolved in runtime by querying the target database whereas the value of <%Container_ServerName%> is taken from the Container Manager settings.
<%Container_ServerAndInstanceName%> – The name of the server and instance that contains a target database, as it was defined in the Container Manager. The value of this variable is synonymous to the value of the combined string <%Container_ServerName%>\<%Container_InstanceName%>.
Using System Variables in SQL Code
The following example (see figure below) illustrates the use of System Variables in SQL code. The first System Variable in this example is surrounded by single upper quotes to be returned as a string from a SELECT statement. The second System Variable is used to call a stored procedure using a fully qualified object name.
