Solved

SQL job output to a Text file

Posted on 2013-06-14
4
2,127 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 25

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 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL recursive CTE challenge... 8 35
partitioning database after decade growth 8 60
MS SQL Conditional WHERE clause 3 38
sql server major issue  need help 2 53
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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