sql-serveransiblewindows-server

Ansible SQL Server query returns an error although it seems to do what it should


I'm just learning Ansible, and I'm writing a playbook to insert values in a SQL Server database running on a Windows server. After some trial and error I have managed to get the playbook working, it inserts the data in the database. But although the values gets inserted where they should, Ansible throws an error.

Running the SQL query directly in the SQL console reports no errors, all seems fine.

I was hoping any one here could help me with this error, and how to clear it.

Here is the error message from Ansible:

fatal: [server.DOMAIN.LOCAL -> localhost]: FAILED! => 
{"changed": true, "error": "Statement not executed or executed statement has no resultset", 
"msg": "query failed", "query": 
"IF NOT EXISTS (SELECT * FROM DB.dbo.Table WHERE Value2 = %(value2)s)\n  
BEGIN\n    
  INSERT INTO DB.dbo.Table (Value1, Value2) VALUES (%(value1)s, %(value2)s)\n 
 END\n"}

And here's the task form the playbook:

   - name: 'Add value to Db table'
 local_action:
  module: community.general.mssql_script
  login_user: "{{ mssql_login_user }}"
  login_password: "{{ mssql_login_password }}"
  login_host: "server.DOMAIN.LOCAL"
  login_port: "1433"
  db: DB
  script: |
      IF NOT EXISTS (SELECT * FROM DB.dbo.Table WHERE Value2 = %(value2)s)
        BEGIN
          INSERT INTO DB.dbo.Table (Value1, Value2) VALUES (%(value1)s, %(value2)s)
        END
  params:
    value2: 'This is value 2'
    value1: 'This is value 1'
 ignore_errors: Yes
 run_once: True

The task runs locally on my test-Mac, as Ansible uses WinRM to run commands on Windows hosts, and WinRM has problems running python modules. (Source)

The error reports

Statement not executed or executed statement has no resultset

It is executed as the data gets inserted into the table, but is there a way to return a result set that gets validated by the mssql module in Ansible?

Thank you!

EDIT: I use the "ignore_errors = true" option to prevent the runbook from stopping at the error.


Solution

  • Solved by @AlwaysLearning's comment.

    SELECT * FROM DB.dbo.Table WHERE Value2 = %(value2)s;
    

    This did the trick.