How to execute a complex SQL statement or to compile a procedure or a function.

[ameyaagashe]

Hello there,

Can you kindly provide us with an example of how to execute a complex SQL Statement, or to compile a procedure or a function?

Is there any way we can execute a SQL file?

Kindly assist.

Thank you

Ameya Agashe

[zigaaa]

This works for me. Pay attention for double quotes: " ' SELECT ... ' "

• name: MSSQL query
  mssql_query:
  login_host: (serverhostname)
  query: "{{ wholeline }}"

ansible-playbook MSSQL_query.yml -i hosts -e wholeline="'SELECT * FROM dbo.Table1 WHERE Hostname IS NULL'"

[ameyaagashe]

@zigaaa Thanks for that but my question is how we go for a SQL procedure or function spreading over multiple lines?

[riyasyash]

I think you can use ansible lookup module to load the sql file and pass that as the query argument.

[nicolaibaralmueller]

I think you can use ansible lookup module to load the sql file and pass that as the query argument.

Hi,

Trying to use your module like so:

- name: enable backup
  mssql_query:
    login_user: sa
    login_password: "{{ sql_admin_password }}"
    db: msdb
    query: >
          EXEC msdb.managed_backup.sp_backup_config_basic   
          @enable_backup = 1   
          ,@database_name = '{{ item }}'
          ,@container_url = '{{ url }}'
          ,@retention_days = 4
  loop: "{{ db_list }}"

However the query times out. A quick debug shows that the output it not correct.

debug:

ok: [XX.XX.XX.XX] => (item=DBNAME) => {
    "msg": "EXEC msdb.managed_backup.sp_backup_config_basic   \n@enable_backup = 1   \n,@database_name = 'DBNAME'\n,@container_url = 'https://querytestbackupstorage01.blob.core.windows.net/backupcontainer'\n,@retention_days = 4\n"
}

Using loops make is rather difficult to use this module :)