Executing SQL Queries on Servers with JAMS

Follow

 

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 Configuration section.
  2. Click on the Add button to add a new Execution Method.
  3. Name the new Execution Method and add a description. In the example below, the new Execution Method is named SQLCMD.
  4. Select Command as the Base Execution Method, then click Next.
  5. Ensure Edit the Execution Method definition after adding is checked, then click Finish.
  6. The Properties dialog for the newly created Execution Method will now appear.
  7. On the Source tab, set Job Module to ParsedSql and the Default File Extension to sql.
  8. On the Execution tab, ensure the Type is set to Batch, and modify the Command process to
    sqlcmd -E -b -e -i "{0}"



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>>

 

Return to Top

 

 

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.
  3. 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.
  4. Select SQLCommand as the Base Execution Method, then click Next.
  5. Ensure Edit the Execution Method definition after adding is checked, then click Finish.
  6. The Properties dialog for the newly created Execution Method will now appear.
  7. On the Parameters tab, double click the ConnectionString parameter to open the Parameter dialog.
  8. Customize the Default Value with the proper connection string to allow JAMS to connect to the SQL Server.
  9. Save and Close the Parameter and Job Properties dialog boxes.
  10. 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.

 

 

Return to Top

 

Have more questions? Submit a request

Comments