Tip: Spacing Requirements for xp_sqlmaint

When troubleshooting the behavior of xp_sqlmaint for the previous Q&A ("Particular Demands of the xp_sqlmaint Command," InstantDoc ID 38442), I found it was convenient for me to use the xp_sqlmaint command's ­U parameter to specify a SQL Server login. I tried to execute a command that looked like this:

EXECUTE master.dbo.xp_sqlmaint N'-Snittanysql2000_1 -U "sa" -P  "Password" -PlanID 7B303E77-8D20-44B6-9D68-156F1A0991CF -Rpt  "C:Program FilesMicrosoft SQL ServerMSSQL$SQL2000_1LOGDB   Maintenance Plan - Simple Plan For Pubs0.txt" -UpdOptiStats 10 '

The code generated an error message from xp_sqlmaint, even after I checked the command syntax several times. I finally noticed that an example in SQL Server Books Online (BOL) had an embedded space between the -S parameter and the value for server name. Adding a space to my command, as follows, did the trick, and the command ran fine:

EXECUTE master.dbo.xp_sqlmaint N'-S nittanysql2000_1 -U "sa" -P  "Password" -PlanID 7B303E77-8D20-44B6-9D68-156F1A0991CF -Rpt  "C:Program FilesMicrosoft SQL ServerMSSQL$SQL2000_1LOGDB  Maintenance Plan - Simple Plan For Pubs0.txt" -UpdOptiStats 10 '

This is one of the few cases where an embedded space between a parameter and its value is absolutely required.