Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136

    Question 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:
    DTSrun
    osql
    bcp

    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.

    SQL Server 2000 SP4

    Any ideas?

    Thanks,
    Reghardt

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Create a DTS package that executes the stored procedure (using its output as a data source), then have the DTS package write that output to a text file.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •