Create comma separated file from SQL Server 2008 with BCP and XP_CMDSHELL

I was assigned a task few days back to generate comma separated file from SQL Server table, It is very easy to do so with BCP utility. Generally we used to use BCP from DOS prompt but herewith I am going to use BCP right from my SSMS with XP_CMDSHELL.


use Adventureworks

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)




–insert records



SELECT ‘Rajan’,‘MIS’,‘mar’



declare @sql varchar(1000)

–creating emps.txt file in D drive from just created table above

select @sql = ‘bcp Adventureworks..emps out d:\emps.txt -c -t, -T -S ‘+ @@servername

–print @sql

–generally, you should run BCP from command prompt, if you wish to do it from

–SSMS, you have to use xp_cmdshell

exec master..xp_cmdshell @sql



If your xp_cmdshell is disable, there is one small article to enable it at:

I have written some more interesting article on BCP which you can find at


Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of

Microsoft SQL Server Blog. Fight the fear of SQL with Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “Create comma separated file from SQL Server 2008 with BCP and XP_CMDSHELL”

  1. You should blog things like this more.
    People will judge you based on your quality.

Comments are closed.