How do I modify a stored procedure to produce results in a designated file

Question

How do I modify a stored procedure to produce results in a designated file. I'm happy with the results I get on a grid in SSMS. I'd like to obtain these results in a designated file while running in visual studio express. I can acheive this result by using datasets but this is not the most efficient way of doing what I want.

Answer 1

depends on how do you want to run the store procedure.

for example, use xp_cmdshell, and specif/define the destination.

or create sql agent job to combine batch, or powershell script .

or add a separated SSIS project if you use visual studio and you want to manupulate the SSIS project result...

Answer 2

You can use SSIS to create the file you need. (execute the stored procedure inside the package.).