Home / server / Questions / 1108301
Accepted
Manu
Asked: 2022-08-17 03:18:03 +0800 CST

Using variables from task in following loop task

  • 772

I want to deploy some stored procedures across multiple MSSQL instances on multiple server. In my playbook I have a task which gathers all the instances on a server which I use in the following tasks to loop everything on any instance. I use this method on many of my playbooks but in this one I also need to be able to use a previously gathered variable in a when condition while looping the task.

These are the relevant parts of my playbook:

#################### GATHER INSTANCES ####################
    - name: list instances
      win_shell: (Find-DbaInstance -ComputerName localhost | Where Availability -eq 'Available').instancename
      changed_when: False
      register: sql_instances

#################### GATHER PROCEDURES ####################
    - name: list procedures
      win_shell: (Get-DbaDbStoredProcedure -SqlInstance localhost\{{ item }} -Database master -Schema dbo).Name
      changed_when: False
      register: sql_procedures
      loop: "{{ sql_instances.stdout_lines }}"  
      
#################### INSTALL CUSTOM PROCEDURES ####################
    - name: my_procedure
      ansible.windows.win_powershell: 
        script: |
          $batch_install_prodecure = @'
            --My Stored Procedure
          '@
          Invoke-DbaQuery -SqlInstance localhost\{{ item }} -Query $batch_install_prodecure
      when: sql_procedures.stdout_lines.find('my_procedure') != 0
      register: my_procedure
      loop: "{{ sql_instances.stdout_lines }}"

This is the output:

#################### GATHER INSTANCES ####################
  "stdout_lines": [
    "MSSQLSERVER2",
    "MSSQLSERVER"
  ]
  
 #################### GATHER PROCEDURES ####################
   "stdout_lines": [
    "sp_Something",
    "sp_Somethingelse"
  ],
  "stderr_lines": [],
  "_ansible_no_log": false,
  "item": "MSSQLSERVER2",
  "ansible_loop_var": "item",
  "_ansible_item_label": "MSSQLSERVER2"
}
############################
  "stdout_lines": [
    "sp_Something",
    "sp_Somethingelse"
  ],
  "stderr_lines": [],
  "_ansible_no_log": false,
  "item": "MSSQLSERVER",
  "ansible_loop_var": "item",
  "_ansible_item_label": "MSSQLSERVER"
}

#################### INSTALL CUSTOM PROCEDURES ####################
The conditional check 'sql_procedures.stdout_lines.find('custProc_Backupcheck') != 0' failed. The error was: error while evaluating conditional (sql_procedures.stdout_lines.find('custProc_Backupcheck') != 0): 'dict object' has no attribute 'stdout_lines'

I kinda get why it does not work. I have to somehow describe with which item the variable is found since it is generated in a loop. I just don't know how to do it.

The furthest I've come so far is by trying to implement something similar as it was done in this post but I guess this method does not apply in my case

I know I could easily just do all this directly in PowerShell but in the end my goal is to install multiple procedures (one per task) and also be able to easily remove older ones for keeping a identical inventory of procedures on my MSSQL instances.

Can someone tell me how I have to set the when condition to achieve my goal?

Edit:

See the accepted answer for why it does not work.

I've now changed to check, weather the procedure is already installed or not, in PowerShell:

#################### INSTALL CUSTOM PROCEDURES ####################
    - name: my_procedure
      ansible.windows.win_powershell: 
        script: |
          $name_procedure = 'my_procedure'
          $install_prodecure = @'
            --My Stored Procedure
          '@
          if ((Get-DbaDbStoredProcedure -SqlInstance localhost\{{ item }}  -Database master -Schema dbo).Name -contains $name_procedure) {
              write-host "OK: SP $name_procedure is already installed"
          } else {
              write-host "SET: SP $name_procedure needs to be installed"
              Invoke-DbaQuery -SqlInstance localhost\{{ item }} -Query $install_prodecure
              if ((Get-DbaDbStoredProcedure -SqlInstance localhost\{{ item }}  -Database master -Schema dbo).Name -contains $name_procedure) {
              write-host "OK: SP $name_procedure sucessfully installed"
              } else {
                  write-host "ERROR: Was not able to install $name_procedure"
              }
          }
      register: my_procedure
      loop: "{{ sql_instances.stdout_lines }}" 
      failed_when: my_procedure.host_out.find('ERROR:') != -1
      changed_when: my_procedure.host_out.find('SET:') != -1
sql-server

0 Answers

  1. Best Answer

    "When you use register with a loop, the data structure placed in the variable will contain a results attribute that is a list of all responses from the module."

    https://docs.ansible.com/ansible/latest/user_guide/playbooks_loops.html#registering-variables-with-a-loop

    so for this:

    register: sql_procedures
loop: "{{ sql_instances.stdout_lines }}"

    the sql_procedures will be an array of dicts containing the results in each element of sql_procedures.results[*].stdout_lines

    A more complex loop would probably help. Have a look at the example of Iterating over nested lists which is practically what you want to achieve (procedures is each server)

    • 1