How To: Export CSV in SQL Server 2008

On one of my earlier posts I explained how to send attachment in SQL Server 2008. The attachment in my case would be a csv exported from the execution of a stored procedure. I used bcp utility command from within SSMS. Since there is "bulk insert" command in SQL server but no bulk export command, I used the bcp utility which has been around ever since the early days of SQL server. So in my case, the query would look like:

sp_configure 'xp_cmdshell', '1'
RECONFIGURE
GO

declare @sql varchar(8000)

select @sql = 'bcp "set fmtonly off exec MyDB..sp_getAdminAllUserStats ''2009-12-01'',''2009-12-31''" queryout c:\Reports\report.csv -c -t, -T'
exec master..xp_cmdshell @sql

In order to use xp_cmdshell stored procedure, I would first need to enable it. The syntax for bcp is explained in detail in http://msdn.microsoft.com/en-us/library/ms188365.aspx. In case you are wondering the use of "set fmtonly off" , without using this you will get a " [Microsoft][ODBC SQL Server Driver]Function sequence error". The "queryout" is used since I am using a query. "-c" is used to specify character type and it is faster than using -n (native type). "-t," is where you transform your export as csv because each column's field terminator will be a comma. To use the current trusted authentication -T is used. Finally, the last statement of the stored procedure needs to be a select statement.

 

That's it.