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:\cdpq\dba\prod\log\DBMaintPlan1.txt" -WriteHistory  -CkDB 
-CkAl -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps

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 —Snittany\sql2000_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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.