SQL Server Commands in JAMS

By default, the SQLCommand Execution Method allows users to execute SQL commands against the default SQL Server instance on the machine where the Job is run.

To execute SQL commands against a SQL instance that is not the default SQL Server instance, JAMS users can employ multiple methods:

1. Create a SQL Command Execution Method that will work with any server
2. Create a Server-Specific SQL Command Execution Method

Create a SQL Command Execution Method that will work with any server

Once created, this Execution Method executes SQL commands against a SQL Server instance as defined by Parameters, or set within the Job's Source.

1. In the JAMS Client, open the Execution Methods shortcut from the Shortcuts.
   
2. Click on the Add button to add a new Execution Method. Name the new Execution Method and add a description. In the example below, the new Execution Method is named SQLCMD. Select Command as the Base Execution Method and ensure Edit After Adding is checked, then click Ok.
   
   
   
3. The Properties dialog for the newly created Execution Method will now appear.
4. Navigate to the Properties tab. Ensure the Method Type is set to Batch, and modify the Command process to

   sqlcmd -E -b -e -i "{0}"

   Then, set the Extension to sql, and Job Module to ParsedSql.
   
   
   
   

The SQL Server instance must be specified on Jobs created with the newly created SQLCMD Execution Method (Execution Method name may vary). This is accomplished by either adding a ConnectionString Parameter to the Job in order to parametize the SQL Server, or by adding the :connect command (examples below) to the Job Source.

:connect MsSqlServer
:connect MsSqlServer\SQLExpress
:connect <<SqlInstance>>
:connect MsSqlServer -U Joe -P <<PasswordForJoe>>

Create a Server-Specific SQL Command Execution Method:

Once configured, this SQLCommand Execution Method allows users to execute SQL Commands against a predefined server, regardless of where the Job may run. 

1. In the JAMS Client, open the Execution Methods shortcut from the Configuration section.
   
2. Click on the Add button to add a new Execution Method. Name the new Execution Method and add a description. It is best practice to differentiate the new Method using a name related to the relevant server. Select SQLCommand as the Base Execution Method and ensure Edit After Adding is checked, then click Ok.
   
3. The Properties dialog for the newly created Execution Method will now appear.
4. On the Parameters tab, add a new ConnectionString parameter with a Default Value of the proper connection string to allow JAMS to connect to the SQL Server.
   
5. 
   
6. Save and Close the Parameter and Job Properties dialog boxes.
7. Users may now create a JAMS Job using the newly created Execution Method, or change an existing Job's Execution Method to reference to newly created Execution Method.