Particular Demands of the xp_sqlmaint Command

I want to use the —Q parameter of the ISQL command when I execute the xp_sqlmaint command. However, I'm getting the following error message from ISQL when I try to execute the xp_sqlmaint command:

EXEC xp_sqlmaint '-PlanName "DB Maintenance Plan1" -Rpt"d:cdpqdbaprodlogDBMaintPlan1.txt" -WriteHistory  -CkDB -CkAl -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps2weeks'

I know the syntax of the xp_sqlmaint command is fine because it works correctly when I run it from SQL Agent as a scheduled job. The command also works fine when I run it directly from Query Analyzer. What's the problem?

Both ISQL and xp_sqlmaint are particular about whether parameters are enclosed in single or double quotes. Consider the following command:

Isql —E —Snittanysql2000_1 —Q"select 1"

This command works fine, but the command won't work if you change the double quotes to single quotes. A variety of single and double quotes are used within the xp_sqlmaint command, and this confuses the parser for ISQL. I tried many combinations of single quotes, double quotes, and escape characters but couldn't find a combination that worked. It worked fine when I executed the command by using the —i switch rather than the —Q switch. Of course the —Q switch accepts a query on the ISQL command line whereas the —i switch specifies a file name that contains the query to execute. Using -i might be a suitable option for you, and it gets around the problems that the particular needs of ISQL and xp_sqlmaint cause.