Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Comma Delimited Result Sets in SQL Server 2005 Management Studio


By:   |   Read Comments   |   Related Tips: More > SQL Server Management Studio

Attend these FREE MSSQLTips webcasts >> click to register


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

  • Column aligned
  • Comma delimited
  • Tab delimited 
  • Space delimited
  • Custom delimiter
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



Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools