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)

) ON [PRIMARY]

GO

 

–insert records

INSERT INTO emps

SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL

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:
http://www.sqlhub.com/2009/05/enable-xpcmdshell-in-sql-server-2008.html

I have written some more interesting article on BCP which you can find at
http://www.sqlhub.com/2009/03/bcp-or-bulk-copy-program-in-sql-server.html
http://www.sqlhub.com/2009/03/bcp-export-data-from-sql-server-to-flat.html
http://www.sqlhub.com/2009/03/bulk-insert-bulk-copy-or-bcp-difference.html

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Enable XP_cmdshell in SQL Server 2008

XP_CMDSHELL is very useful extended stored procedure in Microsoft SQL Server 2008 which will give you power to run command as an operating system power shell. By default it is disabled in any Microsoft SQL server version due to security reasons but you can enable it for your need. Herewith, I am giving you T-SQL commands to enable it.
–list all setting which you can configure with SP_Configure stored procedure
–you may not be able to see XP_CMDSHELL command in the list.
EXECUTE SP_CONFIGURE
–see full list of command by making show advanced options ON
EXECUTE SP_CONFIGURE ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO
–enable xp_cmdshell command
EXECUTE SP_CONFIGURE ‘xp_cmdshell’, ‘1’
RECONFIGURE WITH OVERRIDE
GO
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com