Executing Scripts and Queries on One or More Databases

Executing Scripts and Queries on Multiple Databases in Parallel

The main editor window can be used to either execute scripts and queries on a single database, or deploy scripts and queries against all or some databases defined in a Container in parallel and get aggregated results from all those databases. To start a new script and execute it against multiple databases, a new script can be started in the main editor window by selecting File → New → File from the main menu. If you already have a script window open, in order to run the script against multiple databases you must first ensure that the script window is not already connected to a single database. You can tell that the window is connected to a database by examining the window tab: If the database icon in the tab is yellow as in the following image then the window is connected and you must first disconnect the connection by pressing the Disconnect icon . The window will then be disconnected and the database icon will turn grey. Grey database icon indicates that the script window is not connected to a database.

Figure 67:  An example of a script window connected to a database. You must disconnect the script window to enable the Containers drop down.

To run code against the databases defined in a Container and return unified results in response to all commands and queries, first make sure to load or type the SQL commands you wish to run in the SQL editor window. Next, refer to the toolbar and use the Container drop down to select the Container against which the code will be run, as demonstrated in the following image. If this drop down is disabled then you must first disconnect the script window from its current database connection. Keep in mind that once you instruct Combine to execute the code, Combine will connect and deploy the code on multiple databases and servers simultaneously. If you wish to limit the number of connections that will be initiated from the Combine machine against all servers or limit the total number of connection that will be used for each target server (applicable when multiple target databases reside on the same server), you can do so by using the options menu. Please refer to the section titled Limiting the Number of Connections for more information.

Figure 68:  Selecting the Container - code will be deployed against target databases in the Container in parallel.

At this point you can either choose to run the code or only retrieve estimated execution plans from target databases in the Container without executing the code. To return the estimated execution plans, select Query → Display Estimated Execution Plan from the main menu, or hit the CTRL+L shortcut instead. On the other hand if you wish to execute the code against all Container databases then press the Execute icon (or the F5 shortcut). If you click the arrow in the Execute icon then you can also choose to return the actual execution plan from all databases. You can also direct Combine to return the actual execution plan by pressing the CTRL+M shortcut or selecting Query → Include Actual Execution Plan from the main menu before pressing the Execute icon. After you instruct Combine to start executing the code (or once you request an estimated execution plan) Combine performs a set of tests and verifications to ensure that code will be deployed correctly on all Container databases. The steps taken by Combine are now explained in detail below.

Check that the authentication type and credentials are available for all databases:
Recall that Containers can be configured to use several authentication types, namely SQL authentication, Windows authentication or Prompt (see the Static Container Wizard and Dynamic Container Wizard sections for more information).
Also you are able to modify all SQL server connection options using Advanced button.

If any Containers are defined to use the Prompt authentication type then you will be prompted to enter the authentication type to use to connect to all Container databases. If you choose SQL authentication type you will also be asked to provide a login name and password. These credentials will not be stored by Combine and are only used in run-time to connect and deploy the code on all databases. On the other hand, if the Container is configured in the Container Manager to use SQL authentication or Windows authentication then Combine will not prompt you for the authentication type and credentials and will continue to perform the next test. The image below illustrated the credentials window displayed by Combine when the Prompt authentication type is used. After you enter the credentials in this dialog you must hit the Apply button and then OK to continue.

Figure 69:  The authentication and credentials dialog that appears before executing code against all Container databases when the Container uses the Prompt authentication type.

Resolve all target databases, verify connection and authentication to all target databases and allow the user to deselect databases: At this point Combine has the authentication type and all connection attributes needed to resolve the target databases on which code will be deployed. First, Combine resolves all target databases: For Static Containers, Combine will retrieve the database and server names from the Container Manager. For Dynamic Containers, Combine will run the Dynamic Container query dialog the Reference Static Container to retrieve the database and server names for all target databases (please refer to Static vs. Dynamic Containers to learn more about Container types). After all database and server names are resolved, Combine will attempt to connect to all databases to verify that they indeed exist and that the authentication type provided for the Container in the Container Manager (or in the dialog in the last image) can be used successfully. The results of the connectivity test are then presented in another dialog, which also allows you to deselect Container databases. Code will not be deployed on deselected databases and will only be executed against all selected databases. Also note that databases that cannot be connected successfully (due to timeouts or wrong credentials provided in Combine) will be marked in grey and deselected automatically. You can reselect these databases to try to deploy code against them however this is highly not recommended. To demonstrate the connectivity test dialog, below are two examples of the dialog when the connectivity to all Container databases is successfully verified, and when one database cannot be contacted.

Figure 70:  Database connectivity and authentication validation dialog when Combine connected to all databases successfully. You can deselect databases in this dialog to not run code against the deselected databases.

Figure 71:  Database connectivity and authentication validation dialog when one database cannot be contacted using the provided authentication type and credentials.

Please be aware that this dialog is the last one displayed prior to code execution. If for any reason you wish to abort the code deployment then press cancel in this dialog. If you press OK then code will be executed against all databases marked in the last dialog. The image below demonstrated the case where Cancel is pressed after the connectivity test. Notice that the connectivity results are displayed in the output window.

Figure 72:  The Output window and connectivity test results after execution is aborted.

Deploy code on all selected databases and return execution plans and results: At this point, Combine will deploy the code against all target databases marked in the last dialog. Result sets with matching schema returned from the target databases will be aggregated and displayed in the Results window, where results for each individual database are available as well. If you also requested to return the actual execution plans then the execution plans will be available for each individual target database. If any execution errors occur due to incorrect SQL code or if any messages (such as row counts) are returned from the server, these messages and errors can be found in both the aggregated messages window and the messages window for each database. The output window can also be viewed during code execution to monitor the execution status. Below is an example of the aggregated results and individual results for each database when Combine was instructed to return the actual execution plans.

Figure 73:  Viewing the aggregated results returned from all target databases.

Figure 74:  Viewing the execution plans returned from each target database.

Figure 75:  Viewing the output window to check the execution status.

© 2001-2018 JNetDirect, Inc. All Rights Reserved.