Solved

SQL job output to a Text file

Posted on 2013-06-14
4
1,784 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 250 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 24

Assisted Solution

by:chaau
chaau earned 250 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 24

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

943 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now