0

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.