Kenneth Fisher asks the important Stockdale questions (Who am I? What am I doing here?):
It works when I run it this way™ but not when I run it through xp_cmdshell!
It’s a permissions issue. When you run xp_cmdshell you are running under the ??? account.
Little bit more detail. The extended stored procedure xp_cmdshell creates a windows command shell. This shell has to be run under a windows/active directory account. Obviously you can’t get access to a windows resource (a directory for example) using a SQL Server login. The trick is to be able to tell them what account xp_cmdshell is using within that shell. There are two possibilities here.
Read on to learn about those two possibilities.