Maintenance Plan Wizard generates invalid date parameter in script for delete

Question

The generated SQL for my Maintenance Plan is

EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2019-01-02T10:44:21''

but the execution returns error message

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '2019'.

It dislikes the date parameter! I think it is to do with the fact my server is in Australia not USA, and we view dates differently!

Any ideas how I overcome this bug?

Answer 1

It's not related to the date format, the generated T-SQL from Maintenance Plans doubles up the single quotes (') in the generated script. A double quote escapes a single quote inside quoted text.

For example:

SELECT 'Figure 1'

would return the value "Figure 1", whereas:

SELECT ''Figure 1''

Would throw an incorrect syntax error similar to yours.

When you extract the T-SQL for a Maintenance Plan task, you should copy it into a new query window and do a Find & Replace to replace '' with '. You may have to run it twice depending on the generated script.