Stored Procedure (or) SQL Script Output to Text File

  • Hi All,

    I am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored procedure executes it generates some output text. I need to store this output to text file when ever this store Procedure (or) SQL Script executed by job Scheduler.

    Please help me how to handle this?

    Thanks,

    Anil

  • This article http://thiagsundar.wordpress.com/export-data-to-text-file/ discusses several options for this. Personally, I'd create an SSIS package that executes the stored procedure and has a flat file connection for the destination file. The package can be executed in a job step.

    Greg

  • Hi Charles,

    Thanks for the reply. My Stored Procedure contains the cursor. The output of the stored procedure contains all the Logins and their passwords and default database assigned. This all information when i execute on Query Analyser it will display output on the screen.

    I need to execute this stored procedure from Job scheduler and store the output some where in text file.

    Can you suggest me How you mention path of the file during execution of stored procedure?

    Please help me.

    Thanks,

    Anil

  • Anil Kumar Kurra (4/29/2009)


    Hi All,

    I am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored procedure executes it generates some output text. I need to store this output to text file when ever this store Procedure (or) SQL Script executed by job Scheduler.

    Please help me how to handle this?

    Thanks,

    Anil

    I take it that you mean you have a scheduled job to be run by SQL Server Agent, as opposed to a Scheduled Task to be run by the operating system.

    Open up the job, then open up the step. Go to the advanced tab. Click in the "Output File" and enter the location and filename for the output. Save all changes. When the job is run, it will now pump the output to this file.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Hi,

    I had tried that method. But in that output file it is storing the Job executed Successfully.

    It is not storing the output of the script.

    It is storing Job has executed successfully in that file everytime the job executes.

    Please let me know on this.

    Regards,

    Anil

  • Well, I don't know what to tell you. This is the method that I use, and the log files from the jobs do contain the output from the procedure. From both print statements, and select statements.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Anil Kumar Kurra (4/29/2009)


    Hi Charles,

    Thanks for the reply. My Stored Procedure contains the cursor. The output of the stored procedure contains all the Logins and their passwords and default database assigned. This all information when i execute on Query Analyser it will display output on the screen.

    I need to execute this stored procedure from Job scheduler and store the output some where in text file.

    Can you suggest me How you mention path of the file during execution of stored procedure?

    Please help me.

    Thanks,

    Anil

    I'm talking about executing the stored procedure inside an SSIS package. The output file is designated in the flat file connection manager.

    Greg

  • .

  • Wayne,

    I create a stored procedure and I am executing that stored procedure via job step on the Job Agent creating a text file to an output file location on the advanced tab of the jobstep.

    However, when the job runs and creates the text file there is heading on top of the text file. (see below) do you know how to get rid of the heading so the first actual record.

    Job Diet Orders' : Step 1, 'Run Stored Procedure OrderProcedure' : Began Executing 2010-08-11; 27 :11:28

    ---------- ---------- ------------------------- --------------- - ---------- -------------------------------------------------------------------------------- -------- -------- ---------- ---------- -------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Thank you for your time.

  • .? BT

  • you could create a OS command job stem andcall sqlcmd from the OS with the header option set like so:

    sqlcmd -h-1 -i "D:\scriptdir\script.sql" -o "D:\outputdir\outputtext.out"

    fairly sure that'll give you what you're looking for.

  • NJ-DBA (10/17/2011)


    you could create a OS command job stem andcall sqlcmd from the OS with the header option set like so:

    sqlcmd -h-1 -i "D:\scriptdir\script.sql" -o "D:\outputdir\outputtext.out"

    fairly sure that'll give you what you're looking for.

    just noticed the age of this thread... saw the previous BT ? post... anyhow better late than never?

  • Hi,

    The output file in the SQL Agent works for me too.

    However, I want to run the stored proc from an SSIS package and write all the outputs (PRINT and SELECT command) into a text file. How do I do that?

    Thanks,

    Irina

Viewing 15 posts - 1 through 15 (of 15 total)

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