By: Jeremy Kadlec | Comments | Related: > SQL Server Management Studio
Problem
How can I return a comma delimited result set with my queries? I am currently using SSIS to be able to generate a comma delimited result set to a text file, but this is just over kill. I thought about using the graphical result set and then massaging the data in Excel or just stringing the results together with a comma, but this just seems like the wrong approach. What is the best way to return a comma delimited result with SQL Server 2005 Management Studio?
Solution
Although those other options do work, I would recommend reviewing the native options. In the Query Editor of SQL Server 2005 Management Studio, configuring the result set as comma delimited is a native feature. This can be achieved by the following steps:
| ID | Directions | Screen Shots |
| 1 | Open SQL Server 2005 Management Studio |
|
| 2 | Navigate to Tools | Options | Query Results | SQL Server | Results to Text |
|
| 3 | Select the query format you desire by changing the 'Output format' drop down box
Press 'OK' to save the configuration |
|
| 4 | On the warning screen press the 'OK' button to confirm that the changes will only be applied to new SQL Server Query Editor windows |
|
| 5 | Once you have opened a new Query Editor window, issue your query to validate the results are delimited as expected |
|
Additional Query Editor Features
Now that we have opened up that can of worms, let's introduce some of the additional native features available with SQL Server 2005 Query Editor. Follow the instructions above to access these features from the Management Studio Options interface shown in step 2:
- How to select the Management Studio Startup Window i.e. Object Explorer, New Query Window, Both, Blank.
- Environment | General
- How many files to display in Files | Recent Files
- Environment | General
- How to customize the font face, size, color, etc for the text editor, text results, etc.
- Environment | Fonts and Colors
- How to create custom keyboard short cuts
- Environment | Keyboard
- How to display display line numbers in the interface
- Text Editor | Plain Text | General
- How to configure word wrap in the interface
- Text Editor | Plain Text | General
- How to configure execution settings i.e. SET NOCOUNT, SET NOEXEC, SET PARSEONLY, SET SHOWPLAN_TEXT, SET STATISTICS IO, etc.
- Query Execution | SQL Server | Advanced
- How to configure the maximum number of characters displayed in each column
- Query Results | SQL Server | Results to Text
Next Steps
- When you need to do something tricky with your data, spend the time to see if the native tool set can accomplish your goal.
- If the native tools do not meet the need, then look to build a custom solution.
- Explore the additional options available with SQL Server 2005 Management Studio as a means to expand your knowledge of the tool.
- Stay tuned for additional tips and tricks from MSSQLTips.com with SQL Server 2005 Query Editor, until then check out these tips:
- Stay tuned for additional tips and tricks from MSSQLTips.com with SQL Server 2000 Query Analyzer, until then check out these tips:
- We would like to thank Omri from the MSSQLTips.com community for this tip suggestion.
- Share your tips and tricks with SQL Server by sending an email to [email protected] so we can share them with the community and give you the recognition you deserve.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
