community / community.general / 6.6.8 / module / mssql_script Execute SQL scripts on a MSSQL database | "added in version" 4.0.0 of community.general" Authors: Kris Budde (@kbudde)community.general.mssql_script (6.6.8) — module
Install with ansible-galaxy collection install community.general:==6.6.8
collections: - name: community.general version: 6.6.8
Execute SQL scripts on a MSSQL database.
- 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: 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
name: aliases: - db default: '' description: Database to run script against. type: str output: choices: - dict - default default: default description: - With C(default) each row will be returned as a list of values. See C(query_results). - Output format C(dict) will return dictionary with the column names as keys. See C(query_results_dict). - C(dict) requires named columns to be returned by each query otherwise an error is thrown. type: str params: description: 'Parameters passed to the script as SQL parameters. (''SELECT %(name)s"'' with C(example: ''{"name": "John Doe"}).)'' ' type: dict script: description: - The SQL script to be executed. - Script can contain multiple SQL statements. Multiple Batches can be separated by C(GO) command. - Each batch must return at least one result set. required: true type: str login_host: description: Host running the database. required: true type: str login_port: default: 1433 description: Port of the MSSQL server. Requires I(login_host) be defined as well. type: int login_user: description: The username used to authenticate with. type: str login_password: description: The password used to authenticate with. type: str
query_results: contains: queries: contains: rows: contains: column_value: description: - List of column values. - Any non-standard JSON type is converted to string. example: - Batch 0 - Select 0 returned: success, if output is default type: list description: List of rows returned by query. elements: list type: list description: - 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 C(GO) keyword in I(script) to separate queries. elements: list type: list description: List of batches (queries separated by C(GO) keyword). elements: list returned: success and I(output=default) sample: - - - - Batch 0 - Select 0 - - - Batch 0 - Select 1 - - - - Batch 1 - Select 0 type: list query_results_dict: contains: queries: contains: rows: contains: column_dict: description: - Dictionary of column names and values. - Any non-standard JSON type is converted to string. example: col_name: Batch 0 - Select 0 returned: success, if output is dict type: dict description: List of rows returned by query. elements: list type: list description: - 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. elements: list type: list description: List of batches (queries separated by C(GO) keyword). elements: list returned: success and I(output=dict) sample: - - - - Batch 0 - Select 0 - - - Batch 0 - Select 1 - - - - Batch 1 - Select 0 type: list