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'-Snittany\sql2000_1 -U "sa" -P "Password" -PlanID 7B303E77-8D20-44B6-9D68-156F1A0991CF -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL$SQL2000_1\LOG\DB 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 nittany\sql2000_1 -U "sa" -P "Password" -PlanID 7B303E77-8D20-44B6-9D68-156F1A0991CF -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL$SQL2000_1\LOG\DB 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.
