OK - so I modified the stored proc a bit to have something like this -

EXECUTE @RESULT = MASTER..xp_cmdshell 'bcp "SELECT COL1, COL2 FROM [##results]" queryout "E:\test\TEST1.csv" -c -t, -T -S DWETLSERV1 -oE:\test\BCPCMD_1.txt', NO_OUTPUT

EXEC MASTER..xp_cmdshell 'dir "C:\Program Files\Microsoft SQL Server\90\Tools\binn\*.exe"> E:\test\myfile_1.txt', NO_OUTPUT

EXECUTE @RESULT = MASTER..xp_cmdshell @sql_1, NO_OUTPUT

@sql contains bcp "SELECT COL1, COL2 FROM [##results]" queryout "E:\test\TEST2.csv" -c -t, -T -S DWETLSERV1 -oE:\test\BCPCMD_2.txt

I got TEST1.csv, BCPCMD_1.txt & myfile_1.txt where TEST1.csv contains the result of the SQL query BCPCMD_1.txt contains the output of the first BCP command and myfile_1.txt contains all the exe files in the location mentioned (including bcp) and I have checked the path as well that has the location set as well.

But I didn't get TEST2.csv & BCPCMD_2.txt.

Seems xp_cmdshell is getting trouble running @sql!