outputting stored procedure to a file

[mefisto3]

hey guys,

i am using a MS SQL Server 7. i have a few stored procedures which generate some reports. what i want to do is to have the stored procedure output the report to a file.

does anyone know how to do it? any help is greatly appreciated!

thanks in advance

[krack_it_up]

Well, what type of reports are you wanting to run. How will the reports be accessed? What application will you use to read the reports. It is my experience that it is alot easier to pull data out of a database than it is to push it out. Why not create a view that contains your results or a stored procedure, and then use access, crystal reports, excel, the web or a number of other products to make an odbc connection and pull the data out. If you use sql to do a dump, all you will get is a text file. (With some exceptions)

If you really want to push the data out though, go and make a DTS package.(Data Transformation Services) Create the result set and tell it to output to a text file. To do this, simply go to the entrprise manager, go to a table in your database and right click on it. then go to all tasks and select export. Then follow the wizard. When you are done, you can set the DTS package to run at schedueled times.

A more general way to do it is to go to your server in enterprise manager and select DTS and custom create your own package. Hope this helps. Post back if you have any quesitons.