community / community.general / 8.5.0 / 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 (8.5.0) — module
Install with ansible-galaxy collection install community.general:==8.5.0
collections: - name: community.general version: 8.5.0
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: 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
name: aliases: - db default: '' description: Database to run script against. type: str output: choices: - dict - default default: default description: - With V(default) each row will be returned as a list of values. See RV(query_results). - Output format V(dict) will return dictionary with the column names as keys. See RV(query_results_dict). - V(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. (Query V(''SELECT %(name\)s"'') with V(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 V(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 O(login_host) be defined as well. type: int login_user: description: The username used to authenticate with. type: str transaction: default: false description: - 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. type: bool version_added: 8.4.0 version_added_collection: community.general 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 V(GO) keyword in O(script) to separate queries. elements: list type: list description: List of batches (queries separated by V(GO) keyword). elements: list returned: success and O(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 V(GO) keyword). elements: list returned: success and O(output=dict) sample: - - - - Batch 0 - Select 0 - - - Batch 0 - Select 1 - - - - Batch 1 - Select 0 type: list