?
Solved

SQL job output to a Text file

Posted on 2013-06-14
4
Medium Priority
?
2,917 Views
Last Modified: 2013-06-21
Hello All,
How can I
Output messages in text file produced by SQL Server 2008 job .
Appreciate any thoughts.
0
Comment
Question by:Star79
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
5teveo earned 1000 total points
ID: 39249137
use SQLCMD utility
http://msdn.microsoft.com/en-us/library/ms162773.aspx

sample
command could be...
sqlcmd -i E:\SQLScripts$\Auto\130-100-ESE-Tally\130-100-ESE-Tally.sql c:\OutputDeleteMe.txt

I placed this command in .BAT file and run as needed via task scheduler or stand alone
0
 
LVL 25

Assisted Solution

by:chaau
chaau earned 1000 total points
ID: 39252007
If you do not want to fiddle with the batch files, you can always do it with the job itself. Select a job "Step" that is required to be logged and go to advanced properties. there you will find an option to output the result to a log file. You can also make it "overwrite" the log file, or "append". See the screenshot below
Job Output option
0
 

Author Comment

by:Star79
ID: 39256741
I have created the storeprocedure that extracts the data.Can somebody tell me how to create it as a sql job and it has to out the data to a text file as in the attached file.
How can I put the data on the position as in the file.Not sure if it has to be a job,can I output from the SP to a text file
screenshot.gif
0
 
LVL 25

Expert Comment

by:chaau
ID: 39258061
you can do this very easily with SP and the job. Use PRINT command, I.e.:

PRINT '    Field         Position       Description'
PRINT '   Blank         1-26            Blank'
etc.

This can be put inside a table cursor (if you retrieve these details from a table).
It can also be formatted using STUFF function. The numbers can be formatted using STR function
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question