How to get text output from a scheduled sp on 2005 to a file

Question

How to get text output from a scheduled sp on 2005 to a file

Random Visitor

Old Hand

Points: 384
More actions
January 23, 2012 at 8:45 am

#143090

We don't have SSIS installed so is there any way a Job can be run to direct the output from an executed sp to a text file on a fileshare?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Random Visitor (1/23/2012)
We don't have SSIS installed so is there any way a Job can be run to direct the output from an executed sp to a text file on a fileshare?

There's a couple of ways I can think of, but it depends on your situation.

Can you install a CLR? that would be my best recommendation; it depends on whether you are writing a string, or if you need to write a resultset into a file in some specified format as well.

If you are allowed to have xp_CmdShell enabled, you can do it via bcp or direct command line calls.

if you can give a little more information about your requirement as to what data, and what kind of data, and what the expected format is, we can help you better.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Random Visitor Old Hand Points: 384 More actions · Answer 2

Hi thanks for your interest.

It's a fairly straightforward query that the sp is running to select some data from a single table. I want to be able to output the data in a 'Results to file' type format onto a shared drive. If teh data could be written to a .csv file with column headers and data delimited by commas so much the better. It needs to be scheduled though to run daily. Simple to do with the old 2000 DTS packages not so easy without SSIS in 2005 🙁

savethytrees SSCommitted Points: 1968 More actions · Answer 3

Open the job that contains the command to run the SP. Go to the step that executes the SP then in the top left corner you will see the 'ADVANCED' tab. Click that tab and you will see an option to select an 'Output file' for that job step. If the SP is going to spit some results then those results will get saved in that file.

http://saveadba.blogspot.com/

Blog
http://saveadba.blogspot.com/

Random Visitor Old Hand Points: 384 More actions · Answer 4

Hey thanks - I never even noticed that tab before 🙂 Problem solved.