Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored Procedure (or) SQL Script Output to Text File Expand / Collapse
Author
Message
Posted Wednesday, April 29, 2009 4:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 30, 2009 8:27 PM
Points: 7, Visits: 16
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
Post #706607
Posted Wednesday, April 29, 2009 9:14 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 4,064, Visits: 5,346
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
Post #706930
Posted Wednesday, April 29, 2009 8:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 30, 2009 8:27 PM
Points: 7, Visits: 16
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
Post #707333
Posted Wednesday, April 29, 2009 8:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 5,365, Visits: 8,967
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #707337
Posted Wednesday, April 29, 2009 8:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 30, 2009 8:27 PM
Points: 7, Visits: 16
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
Post #707339
Posted Wednesday, April 29, 2009 8:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 5,365, Visits: 8,967
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #707354
Posted Thursday, April 30, 2009 10:39 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 4,064, Visits: 5,346
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
Post #707828
Posted Monday, January 4, 2010 11:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 28, 2014 8:16 AM
Points: 155, Visits: 18
.
Post #841620
Posted Friday, August 27, 2010 1:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 4:32 PM
Points: 16, Visits: 158
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.
Post #976703
Posted Monday, October 17, 2011 1:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 8:39 AM
Points: 1, Visits: 30
.? BT
Post #1191588
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse