community.general.mssql_script module – Execute SQL scripts on a MSSQL database

Note

This module is part of the community.general collection (version 9.5.1).

You might already have this collection installed if you are using the ansible package. It is not included in ansible-core. To check whether it is installed, run ansible-galaxy collection list.

To install it, use: ansible-galaxy collection install community.general. You need further requirements to be able to use this module, see Requirements for details.

To use it in a playbook, specify: community.general.mssql_script.

New in community.general 4.0.0

Synopsis 

Execute SQL scripts on a MSSQL database.

Requirements 

The below requirements are needed on the host that executes this module.

pymssql

Parameters 

Parameter	Comments
login_host string / required	Host running the database.
login_password string	The password used to authenticate with.
login_port integer	Port of the MSSQL server. Requires `login_host` be defined as well. Default: `1433`
login_user string	The username used to authenticate with.
name aliases: db string	Database to run script against. Default: `""`
output string	With `default` each row will be returned as a list of values. See `query_results`. Output format `dict` will return dictionary with the column names as keys. See `query_results_dict`. `dict` requires named columns to be returned by each query otherwise an error is thrown. Choices: `"dict"` `"default"` ← (default)
params dictionary	Parameters passed to the script as SQL parameters. (Query `'SELECT %(name)s"'` with `example: '{"name": "John Doe"}`.)’
script string / required	The SQL script to be executed. Script can contain multiple SQL statements. Multiple Batches can be separated by `GO` command. Each batch must return at least one result set.
transaction boolean added in community.general 8.4.0	If transactional mode is requested, start a transaction and commit the change only if the script succeed. Otherwise, rollback the transaction. If transactional mode is not requested (default), automatically commit the change. Choices: `false` ← (default) `true`

Attributes 

Attribute	Support	Description
check_mode	Support: partial The script will not be executed in check mode.	Can run in `check_mode` and return changed status prediction without modifying target.
diff_mode	Support: none	Will return details on what has changed (or possibly needs changing in `check_mode`), when in diff mode.

Notes 

Note

Requires the pymssql Python package on the remote host. For Ubuntu, this is as easy as pip install pymssql (See ansible.builtin.pip.)

Examples 

- name: Check DB connection
  community.general.mssql_script:
    login_user: "{{ mssql_login_user }}"
    login_password: "{{ mssql_login_password }}"
    login_host: "{{ mssql_host }}"
    login_port: "{{ mssql_port }}"
    db: master
    script: "SELECT 1"

- name: Query with parameter
  community.general.mssql_script:
    login_user: "{{ mssql_login_user }}"
    login_password: "{{ mssql_login_password }}"
    login_host: "{{ mssql_host }}"
    login_port: "{{ mssql_port }}"
    script: |
      SELECT name, state_desc FROM sys.databases WHERE name = %(dbname)s
    params:
      dbname: msdb
  register: result_params
- assert:
    that:
      - result_params.query_results[0][0][0][0] == 'msdb'
      - result_params.query_results[0][0][0][1] == 'ONLINE'

- name: Query within a transaction
  community.general.mssql_script:
    login_user: "{{ mssql_login_user }}"
    login_password: "{{ mssql_login_password }}"
    login_host: "{{ mssql_host }}"
    login_port: "{{ mssql_port }}"
    script: |
      UPDATE sys.SomeTable SET desc = 'some_table_desc' WHERE name = %(dbname)s
      UPDATE sys.AnotherTable SET desc = 'another_table_desc' WHERE name = %(dbname)s
    transaction: true
    params:
      dbname: msdb

- name: two batches with default output
  community.general.mssql_script:
    login_user: "{{ mssql_login_user }}"
    login_password: "{{ mssql_login_password }}"
    login_host: "{{ mssql_host }}"
    login_port: "{{ mssql_port }}"
    script: |
      SELECT 'Batch 0 - Select 0'
      SELECT 'Batch 0 - Select 1'
      GO
      SELECT 'Batch 1 - Select 0'
  register: result_batches
- assert:
    that:
      - result_batches.query_results | length == 2  # two batch results
      - result_batches.query_results[0] | length == 2  # two selects in first batch
      - result_batches.query_results[0][0] | length == 1  # one row in first select
      - result_batches.query_results[0][0][0] | length == 1  # one column in first row
      - result_batches.query_results[0][0][0][0] == 'Batch 0 - Select 0'  # each row contains a list of values.

- name: two batches with dict output
  community.general.mssql_script:
    login_user: "{{ mssql_login_user }}"
    login_password: "{{ mssql_login_password }}"
    login_host: "{{ mssql_host }}"
    login_port: "{{ mssql_port }}"
    output: dict
    script: |
      SELECT 'Batch 0 - Select 0' as b0s0
      SELECT 'Batch 0 - Select 1' as b0s1
      GO
      SELECT 'Batch 1 - Select 0' as b1s0
  register: result_batches_dict
- assert:
    that:
      - result_batches_dict.query_results_dict | length == 2  # two batch results
      - result_batches_dict.query_results_dict[0] | length == 2  # two selects in first batch
      - result_batches_dict.query_results_dict[0][0] | length == 1  # one row in first select
      - result_batches_dict.query_results_dict[0][0][0]['b0s0'] == 'Batch 0 - Select 0'  # column 'b0s0' of first row

Return Values 

Common return values are documented here, the following are the fields unique to this module:

Key	Description
query_results list / elements=list	List of batches (queries separated by `GO` keyword). Returned: success and `output=default` Sample: `[[[["Batch 0 - Select 0"]], [["Batch 0 - Select 1"]]], [[["Batch 1 - Select 0"]]]]`
queries list / elements=list	List of result sets of each query. If a query returns no results, the results of this and all the following queries will not be included in the output. Use the `GO` keyword in `script` to separate queries. Returned: success
rows list / elements=list	List of rows returned by query. Returned: success
column_value list / elements=string	List of column values. Any non-standard JSON type is converted to string. Returned: success, if output is default Sample: `["Batch 0 - Select 0"]`
query_results_dict list / elements=list	List of batches (queries separated by `GO` keyword). Returned: success and `output=dict` Sample: `[[[["Batch 0 - Select 0"]], [["Batch 0 - Select 1"]]], [[["Batch 1 - Select 0"]]]]`
queries list / elements=list	List of result sets of each query. If a query returns no results, the results of this and all the following queries will not be included in the output. Use ‘GO’ keyword to separate queries. Returned: success
rows list / elements=list	List of rows returned by query. Returned: success
column_dict dictionary	Dictionary of column names and values. Any non-standard JSON type is converted to string. Returned: success, if output is dict Sample: `{"col_name": "Batch 0 - Select 0"}`

Authors

Kris Budde (@kbudde)

community.general.mssql_script module – Execute SQL scripts on a MSSQL database

Synopsis

Requirements

Parameters

Attributes

Notes

Examples

Return Values