community.general.mssql_script module – Execute SQL scripts on a MSSQL database
Note
This module is part of the community.general collection (version 8.6.4).
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 |
---|---|
Host running the database. |
|
The password used to authenticate with. |
|
Port of the MSSQL server. Requires Default: |
|
The username used to authenticate with. |
|
Database to run script against. Default: |
|
With Output format
Choices:
|
|
Parameters passed to the script as SQL parameters.
(Query |
|
The SQL script to be executed. Script can contain multiple SQL statements. Multiple Batches can be separated by Each batch must return at least one result set. |
|
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:
|
Attributes
Attribute |
Support |
Description |
---|---|---|
Support: partial The script will not be executed in check mode. |
Can run in |
|
Support: none |
Will return details on what has changed (or possibly needs changing in |
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 |
---|---|
List of batches (queries separated by Returned: success and Sample: |
|
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 Returned: success |
|
List of rows returned by query. Returned: success |
|
List of column values. Any non-standard JSON type is converted to string. Returned: success, if output is default Sample: |
|
List of batches (queries separated by Returned: success and Sample: |
|
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 |
|
List of rows returned by query. Returned: success |
|
Dictionary of column names and values. Any non-standard JSON type is converted to string. Returned: success, if output is dict Sample: |