[Bug] Cannot create schedule, using lowlydba.sqlserver.agent_job_schedule:

[solo-untld]

Ansible playbook

- name: Create Job
  lowlydba.sqlserver.agent_job:
    description: "Daily shrink databases"
    enabled: true
    force: true
    job: "Daily shrink databases"
    sql_instance: "{{ sql_instance }}"
    sql_username: "{{ sql_username }}"
    sql_password: "{{ sql_password }}"
    owner_login: "sa"
    state: present

- name: Create a job step
  lowlydba.sqlserver.agent_job_step:
    job: "Daily shrink databases"
    sql_instance: "{{ sql_instance }}"
    sql_username: "{{ sql_username }}"
    sql_password: "{{ sql_password }}"
    step_name: "Get bases, Generate exec string, Shrink one by one"
    step_id: 1
    command: "<TSQL>"

- name: Job schedule
  lowlydba.sqlserver.agent_job_schedule:
    job: "Daily shrink databases"
    schedule: "DailySchedule"
    sql_instance: "{{ sql_instance }}"
    sql_username: "{{ sql_username }}"
    sql_password: "{{ sql_password }}"
    #force: true
    enabled: false
    #state: present
    frequency_interval: EveryDay
    frequency_type: Daily
    start_date: "2022-02-15"
    start_time: "010000"

Execution of code:

TASK [mssql-windows : Create Job] ******************************************************************************************************************************************************************************************
ok: [dev-mssql-db1]

TASK [mssql-windows : Create a job step] ************************************************************************************************************************************************************************************************************************
ok: [dev-mssql-db1]

TASK [mssql-windows : Job schedule] *****************************************************************************************************************************************************************************************************************************
fatal: [dev-mssql-db1]: FAILED! => {"changed": false, "msg": "Error configuring SQL Agent job schedule."}

How to create shedule for job?

[lowlydba]

Can you try increasing the verboseness and running with -vvvv to see if there are any more error details?

[solo-untld]

- name: Job schedule
  lowlydba.sqlserver.agent_job_schedule:
    job: "Daily shrink databases"
    schedule: "DailySchedule"
    sql_instance: "{{ sql_instance }}"
    sql_username: "{{ sql_username }}"
    sql_password: "{{ sql_password }}"
    force: true
    enabled: false
    #state: present
    frequency_interval: EveryDay
    frequency_type: Daily
    start_date: "2022-02-15"
    start_time: "010000"

OUT:

EXEC (via pipeline wrapper)
The full traceback is:
String was not recognized as a valid DateTime.
At line:97864 char:9
+         throw $records[0]
+         ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (<FQDN>:String) [], Exception
    + FullyQualifiedErrorId : dbatools_New-DbaAgentSchedule

ScriptStackTrace:
at Stop-Function, <No file>: line 97864
at New-DbaAgentSchedule<Process>, <No file>: line 55541
at <ScriptBlock>, <No file>: line 140
fatal: [<FQDN>]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "enabled": false,
            "end_date": null,
            "end_time": null,
            "force": true,
            "frequency_interval": "EveryDay",
            "frequency_recurrence_factor": null,
            "frequency_relative_interval": null,
            "frequency_subday_interval": null,
            "frequency_subday_type": null,
            "frequency_type": "Daily",
            "job": "Daily shrink databases",
            "schedule": "DailySchedule",
            "sql_instance": "<FQDN>",
            "sql_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "sql_username": "sa",
            "start_date": "2022-02-15",
            "start_time": "010000",
            "state": "present"
        }
    },
    "msg": "Error configuring SQL Agent job schedule."
}

If I use end date and end time and force turn to false:

- name: Job schedule
  lowlydba.sqlserver.agent_job_schedule:
    job: "Daily shrink databases"
    schedule: "DailySchedule"
    sql_instance: "{{ sql_instance }}"
    sql_username: "{{ sql_username }}"
    sql_password: "{{ sql_password }}"
    force: false
    enabled: false
    #state: present
    frequency_interval: EveryDay
    frequency_type: Daily
    end_date: "2024-02-25"
    end_time: "010000"
    start_date: "2022-02-15"
    start_time: "010000"

OUT:

EXEC (via pipeline wrapper)
The full traceback is:
String was not recognized as a valid DateTime.
At line:97864 char:9
+         throw $records[0]
+         ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (<FQDN>:String) [], Exception
    + FullyQualifiedErrorId : dbatools_New-DbaAgentSchedule

ScriptStackTrace:
at Stop-Function, <No file>: line 97864
at New-DbaAgentSchedule<Process>, <No file>: line 55541
at <ScriptBlock>, <No file>: line 140
fatal: [<FQDN>]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "enabled": false,
            "end_date": "2024-02-25",
            "end_time": "010000",
            "force": false,
            "frequency_interval": "EveryDay",
            "frequency_recurrence_factor": null,
            "frequency_relative_interval": null,
            "frequency_subday_interval": null,
            "frequency_subday_type": null,
            "frequency_type": "Daily",
            "job": "Daily shrink databases",
            "schedule": "DailySchedule",
            "sql_instance": "<FQDN>",
            "sql_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "sql_username": "sa",
            "start_date": "2022-02-15",
            "start_time": "010000",
            "state": "present"
        }
    },
    "msg": "Error configuring SQL Agent job schedule."
}

[lowlydba]

Thanks! That function requires using yyyyMMdd format. You can see in this collections tests that is the format used.

[solo-untld]

So fix documentation

[solo-untld]

And one more to fix - if use force parametr end_date will be yyyy-MM-dd and also will generate error. So now it perfectly works only without "force" and specified manually end_date and start_date in yyyyDDmm format, not like in docs

ex.:

- name: Job schedule
  lowlydba.sqlserver.agent_job_schedule:
    job: "Set log file max size"
    schedule: "Daily at 01:00"
    sql_instance: "{{ sql_instance }}"
    sql_username: "{{ sql_username }}"
    sql_password: "{{ sql_password }}"
    #force: true
    enabled: true
    state: present
    frequency_interval: EveryDay
    frequency_type: Daily
    start_date: "20240223"
    start_time: "020000"
    end_date: "20990225"
    end_time: "020000"

[lowlydba]

I believe the other behavior is upstream, from https://github.com/dataplat/dbatools/issues/9256

[lowlydba]

The fix for dbatools is now available on https://github.com/dataplat/dbatools/releases/tag/v2.1.9 and I have updated the docs in the attached PR to this issue. If you continue to have problems, please open a new issue.