Jeff Moden (2/10/2014)
Eugene Elutin (2/10/2014)
Jeff Moden (2/10/2014)
MyDoggieJessie (2/10/2014)
Of course, you make valid points - 😉 I originally suggested SSIS, but provided a TSQL alternative - not pretty, but there it is...The use of xp_CmdShell shouldn't be a problem for anyone if it's done correctly and it's pretty easy to do correctly especially security wise.
I would agree that, use of xp_CmdShell isn't a problem. However, taking in count much better methods and standard solution designs available nowadays, it would be the last thing i would use, especially for this sort of functionality...
What better methods? SSIS? PoSH? Heh... just because you can do something in SSIS or PoSH, doesn't mean you should. 😀
It's not point to argue on general use of the xp_cmdshell (with proper security setup).
For this particular task, in my humble opinion, it is not the best option.
First of all if using with BCP out, permanent table should be used and some sort of "token" control implemented to allow for concurrent execution, which add unnecessary complexity to the simple logic.
Actually, xp_CmdShell can directly write data into file (using dos echo), but still I would not want SQL process to create such text/log files on a server...
Why not just a simple script (in whatever scripting language) which executes proc and outputs into file in a better location.
This way your proc can be executed ad-hoc to show data on screen (in SSMS), or report can be created based on this proc or whatever... Much more flexible, simpler and robust.