Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
