Microsoft SQL Server Microsoft SQL Server: Client

Summary

This module contains actions for running queries & stored procedures on a Microsoft SQL Server.

Running Queries

See Using Placeholders before using the select, insert, & update actions.

Placeholders are not required when using the stored_procedure action.

Actions

mssql.client.

input_table

Display a list of records in a table, & allow the task operator to make a selection.

Minimum Plugin Version: 2.0.0

Input

  • text: the title of the table

  • records: a list of dictionaries to display in the table

  • minimum: The minimum number of acceptable selections (Optional)

  • maximum: The maximum number of acceptable selections (Optional)

Output

A list of dictionaries.

Tip

  • If neither a minimum or maximum is provided, the task operator will be able to submit 0 selections.

  • If minimum or maximum are provided, the operation will repeat until a valid number of selections is made.

Example

Getting all records from a users table, displaying it and waiting for a single selection:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
- mssql.client.select:
    query: SELECT * FROM users
  load:
    mssql: mssql_secrets
  save: users

- mssql.client.output_table:
    text: Select a user
    minimum: 1
    maximum: 1
  load:
    records: users

mssql.client.

insert

Run an INSERT SQL query.

Minimum Plugin Version: 2.0.0

Input

  • mssql: an MSSQLServer dictionary

  • query: the SQL Query to run

  • placeholders: Optional Placeholders to format in to the SQL query before it is executed.

Output

Nothing is outputted by this action.

Example
1
2
3
4
5
6
7
- mssql.client.insert:
    query: INSERT INTO users(username, name) VALUES(?, ?)
    placeholders:
      - "{{ username }}"
      - "{{ name }}"
  load:
    mssql: mssql_secrets

mssql.client.

output_table

Display a list of records in a table.

Minimum Plugin Version: 2.0.0

Input

  • text: the title of the table

  • groups: a single or list of dictionaries

Output

Nothing is outputted by this action.

Example

Getting all records from a users table and displaying it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
- mssql.client.select:
    query: SELECT * FROM users
  load:
    mssql: mssql_secrets
  save: users

- mssql.client.output_table:
    text: All Users
  load:
    records: users

mssql.client.

select

Run a SELECT SQL query.

Minimum Plugin Version: 2.0.0

Input

  • mssql: an MSSQLServer dictionary

  • query: the SQL Query to run

  • placeholders: Optional Placeholders to format in to the SQL query before it is executed.

Output

A list of dictionaries.

Tip

For more information, regarding the output from this action, see Running Select Queries.

Example
1
2
3
4
5
6
7
- mssql.client.select:
    query: SELECT * FROM users WHERE name LIKE '%?%'
    placeholders:
      - "{{ name }}"
  load:
    mssql: mssql_secrets
  save: users

mssql.client.

stored_procedure

Run a stored procedure.

Minimum Plugin Version: 2.0.0

Input

  • mssql: an MSSQLServer dictionary

  • name: the name of the stored procedure (must start with sp_)

  • parameters: Optional dictionary of parameters to pass in to the stored procedure

Filtered Column Types

Only columns with the following data types are outputted by this action:

  • text (string)
  • numbers (integer & float)
  • boolean (true & false)
  • null

Columns such as SID are removed as PPA receives them in bytes format, which is not JSON compatible.

Output

A list of dictionaries.

Example 1 - Without Parameters
1
2
3
4
5
- mssql.client.stored_procedure:
    name: sp_helplogins
  load:
    mssql: mssql_secrets
  save: user_logins
Example 2 - With Parameters
1
2
3
4
5
6
7
- mssql.client.stored_procedure:
    name: sp_helpuser
    parameters:
      name_in_db: domain\example.user
  load:
    mssql: mssql_secrets
  save: user_info

mssql.client.

update

Run an UPDATE SQL query.

Minimum Plugin Version: 2.0.0

Input

  • mssql: an MSSQLServer dictionary

  • query: the SQL Query to run

  • placeholders: Optional Placeholders to format in to the SQL query before it is executed.

Output

Nothing is outputted by this action.

Example
1
2
3
4
5
6
7
- mssql.client.update:
    query: UPDATE users SET surname = ? WHERE id = ?
    placeholders:
      - "{{ surname }}"
      - "{{ user_id }}"
  load:
    mssql: mssql_secrets