Unanswered: Output to file (not using xp_cmdshell)
Hi everyone -
I have a client who need to output some text from a stored procedure to a text file. The main problem is that it is a SOX system, so we can't use xp_cmdshell, and we can't create new tables. This rules out the following methods:
I then thought well maybe we can convert the SP to a DTS package. Well that can't work because he stores the text to be output in a #temptable, and I couldn't get DTS to do data transformation on a temptable.
Or better yet, just execute the stored procedure via OSQL and direct the text output into the file.
Just FYI, there is absolutely nothing in SOX that prohibits the use of xp_cmdshell. The formal spec doesn't even refer to how queries can or can not be executed, only that the queries must be run either by an automated, auditable process or manually by a named administrator.