Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Stored Procedure (or) SQL Script Output to...
Add to briefcase
12 posts, Page 1 of 2
12»»

Stored Procedure (or) SQL Script Output to Text File Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Anil Kumar Kurra
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
Greg Charles
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: Yesterday @ 11:34 AM
Points: 4,007, Visits: 4,857
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
Anil Kumar Kurra
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
WayneS
Posted Wednesday, April 29, 2009 8:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
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
Anil Kumar Kurra
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
WayneS
Posted Wednesday, April 29, 2009 8:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
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
Greg Charles
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: Yesterday @ 11:34 AM
Points: 4,007, Visits: 4,857
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
Therese Hutcheson
Posted Monday, January 04, 2010 11:40 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 13, 2012 11:57 AM
Points: 152, Visits: 15
.
Post #841620
TurnerC
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
brett-531408
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: Monday, October 17, 2011 1:53 PM
Points: 1, Visits: 6
.? BT
Post #1191588
« Prev Topic | Next Topic »

Add to briefcase
12 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.