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

Note

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

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.

Aliases: database.mssql.mssql_script

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)

Collection links

• Issue Tracker
• Repository (Sources)
• Submit a bug report
• Request a feature
• Communication