Stored Procedure (or) SQL Script Output to Text File

Question

Stored Procedure (or) SQL Script Output to Text File

Anil Kumar Kurra

Right there with Babe

Points: 725
More actions
April 29, 2009 at 4:16 am

#384466

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

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

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

Anil Kumar Kurra Right there with Babe Points: 725 More actions · Answer 2

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

WayneS SSC Guru Points: 95460 More actions · Answer 3

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

Anil Kumar Kurra Right there with Babe Points: 725 More actions · Answer 4

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

WayneS SSC Guru Points: 95460 More actions · Answer 5

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

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

Therese Hutcheson SSC Eights! Points: 833 More actions · Answer 7

Therese Hutcheson

SSC Eights!

Points: 833

January 4, 2010 at 11:40 am

#1098549

.

TurnerC SSC Veteran Points: 290 More actions · Answer 8

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.

brett-531408 SSC Journeyman Points: 83 More actions · Answer 9

brett-531408

SSC Journeyman

Points: 83

October 17, 2011 at 1:11 pm

#1395548

.? BT

NJ-DBA SSChampion Points: 13834 More actions · Answer 10

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 SSChampion Points: 13834 More actions · Answer 11

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?

vkijust4u SSC Enthusiast Points: 130 More actions · Answer 12

http://www.javaquery.com/2013/08/how-to-create-log-file-fromfor-stored.html

vkijust4u SSC Enthusiast Points: 130 More actions · Answer 13

http://www.javaquery.com/2013/08/how-to-create-log-file-fromfor-stored.html

irina hossain Old Hand Points: 398 More actions · Answer 14

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