Output to file from proc

  • Hi All,

    I have a procedure for upgrade. It is usual run manually from QA. It's very important for the users to print what is doing, time for everything, errors(if any), Success-Fail. It works perfect, but now DEV wants to call it from some kind of a script - like upgrade script called from CMD. It's not app and it doesn't have UI for it. It'll print some message to the users, but cannot print the output of my proc. The proc will be called multiple times with different params as user input or pre-defined, by users choice. They want to keep the same output form the proc.

    What will be the most efficiant way to print/save to file the old SP output in the new situation?

    Thanks a lot for any idea.mj

  • If running from a scheduled batch job in a CMD window, convert the script to a stored proc, call it from OSQL, and use (> ) or the (-o) option to route the output to a file name.  Then, have the batch email the output file to the users.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • No this will not work for me... The SP will be called from a cmd script among a lot of other stuff, which somebody else writes...still no idea what's inside. Is there a way to print the SP output, or to save it to file. I do not think that the script will be able to handle the SP output. Also, I cannot change the calling script provided.

    Thanks a lot.

  • I use the following proc that originally came from 4GuysFromRolla but modified it from isql to osql.

    ALTER procedure dbo.xOutputToFile

    (

    @sServer varchar(30),

    @sDB varchar(30),

    @sUser varchar(30),

    @sPWD varchar(30),

    @sOutFile varchar(255),

    @sSQL varchar(1000)

    )

    as

    --From 4GuysFrom Rolla

    --Demonstrate how to export a file from SQL Server

    set nocount on

    CREATE TABLE #trash (out varchar(1000) null )

    declare @sXP varchar(1000),

    @nMaxWidth int

    select @nMaxWidth = 8000

    select @sXP = 'osql' +

    ' -d' + @sDB +

    ' -U' + @sUser +

    ' -P' + @sPWD +

    ' -S' + @sServer +

    ' -h-1 ' +

    ' -w' + convert(varchar,@nMaxWidth) +

    ' -n' +

    ' -Q"' + @sSQL + '"' +

    ' -o' + @sOutFile

    insert into #trash(out)

    exec master..xp_cmdshell @sXP

    SELECT * FROM #trash

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply