If we want to export the result of an ad-hoc query or Stored procedure from SQL Server Management Studio without writing any scripts then check this out.
One of the easiest option if you are using SQL Server 2005 or above is to make use of the output format specifier in SQL Server Management Studio.
Step1: Open SQL Server Management Studio.
Step2: Click on New Query
Step3: Goto Menu Query >> Query Options >> Within Results Choose Text
Step4: On the right window you can see an option called "Output Format". Select "Comma Delimited".
Step5: Next either Press Control + T (to select Results to Text option). Or goto Menu Query >> Results To >> Results to Text option.
Now you are all set. Just run your SELECT statement or SP with the SELECT statement to see the result as Comma Separated Values on the screen.
You can copy and save it as a file now.
You don't have to change any formats for this option. You just execute your SP or SELECT statement and once you got the output on the Results Window. Just "Right click" on the result and choose "Save ResultAs" to save it as CSV file!
I had given these options along with few more options which needed scripting as an response to this forum question.