In this short Blog post let’s see how to fetch results in a Custom/pipe delimited format file directly from SSMS without any fancy SSIS Packages involved. In fact you can use SSMS for fetching result set as with any delimited value.
As you all know with all the default settings of your SSMS without any options enabled/disabled, we’ll get results of any SELECT Statement in a grid fashion as shown below. 
In the above screenshot I pulled few columns from “AdventureWorks” Database with the default Settings. You can copy the results including column headers and paste into excel and create a delimited file. Or if you are looking for CSV or Tab Delimited file, you can achieve without much pain by just right click in the result set and choosing “Save results as” and selecting the required file format. Let’s assume the requirement is to create a Pipe(|) delimited file with column names as the first row in the file, or some other custom delimiter in the file. You can’t see those options while saving the results by default. But, it’s very simple to achieve this just by turning on few options in your SSMS.
Go to Tools->Options->Query Results->SQL Server->Results to Text as shown below
In the top right corner drop down the list and select Custom delimiter as shown below.
Once you choose Custom Delimiter, you’ll have an option to enter the Delimiter as you can see below.(I entered “|” for Pipe Delimited files in this example)
Click Ok and now the trick is to select “Results to Text” in your SSMS while executing the Query and you get a result set with a clean pipe delimiters as you can see below:)
Or choose “results to file” and execute the same to save the results directly to a file. You can see in the below Screenshot where I’ve selected the required option in my SSMS and saving the file as “Pipe”.
Now, I’ve navigated to that folder and opened the file with notepad where I got the file exactly as I was expecting to be as you can see below 🙂
As you can see we don’t need any fancy tools to get custom delimited results…All it takes is exploring our own SSMS 🙂
Hope this helps!
MSSQLTREK 
I tried this in SQL 2012. Instructions were excellent. But it always behaves as if I selected the column aligned option. Any ideas? I’ve tried different delimiters. I’ve tried results to text and results to file with the delimited option, and it always behaves as the column aligned option.
I have the same issue – behaves as if column aligned.
I have the same problem as Grant Brimhall. “:I’ve tried different delimiters. I’ve tried results to text and results to file with the delimited option, and it always behaves as the column aligned option.”
Any ideas?
That’s interesting. I’ve got SSMS 2014 on my laptop, Just tested it again and it’s working as expected with my custom delimiter! Just wanted to double check, you are opening a new query pane to execute your query after the option has been set right?
Turns out it needs to be set by options under the query menu. …under output to text.
Maybe it’s a SSMS 2008 R2 specific difference.
close out SSMS and reopen if you are having issue with the setting working properly, after that worked like a charm!
Interesting we all had the same problem. I figured it out. The options you change only seem to affect new tabs, not any existing tabs.
That’s correct.
Does anyone know how to schedule this as a job?
This blog post explains how to do this on Ad-Hoc basis from a client tool(SSMS in this case). To automate and schedule, you can use a simple SSIS package.
Sreekanth, I found this very useful. Your post cut straight to the solution I needed.
Many thanks for sharing your knowledge.
Tony
Great, I can get a pipe delimeter. But how about the text quote encapsulation? I do not see how to do that with the Options screen. I tried adding them as a delimeter set, but only “1” delimeter ids allowed with SQL 2014
This is good. After struggling with getting pipe separated for months, Worked excellently well.