Using BCP command to create a CSV file from SQL Server

Today, we will review BCP client utility and XP_CMDSHELL to extract information from SQL Server into a csv file. Suppose you have a punch processing application which is used by employees to clock in and clock out. This information is stored in a database. Every night a SQL Server agent job runs which selects all the payroll hours for that day and extract into a csv file, saving it to a network location. The file can then be imported into a payroll processing application such as ADP.

In this article, we will review generating and extracting the csv file to a network location. In future post we will extend this further to FTP the file to a remote FTP Server.

So, let's get started.

1. Create a stored procedure that can be run as a step 1 in a SQL Agent Job.

CREATE PROC GetPayrollPunches
  AS
     BEGIN
                SELECT E.FirstName,E.LastName,P.Hours 
                FROM Employees E INNER JOIN 
               PayrollHours P ON E.EmployeeID = P.EmployeeID
    END
 GO

2. Create a BCP command as a second step in SQL Agent Job.

DECLARE @FileName varchar(50)
DECLARE @Command varchar(2000)

SET @FileName = 'C:\temp\payroll.csv'
SET @Command = 'bcp "EXEC tempdb..GetPayrollPunches" queryout ' + @FileName + '-t -c -T -S MyServer\SQL2008'
--Print (@Command)
EXEC master..xp_cmdshell @Command
GO

Note, you only have to pass server name when server is not default instance or you are running the utility from a different machine.

Switches

• -c output in ASCII
• -t  override field terminator with "," (instead of default tab)
• -T use a trusted connection. You can pass -U and -P for username and password instead.
• -S Server name. If the SQL Server is detault instance, you don't have to pass this parameter.

By default, xp_cmdshell is not enabled. You can enable it using the following command..

RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

BCP is a nice little utility to export data into a file format from SQL Server. I personally don't think this is a very elegant solution. Ideally, there should be an application to run the stored procedure, retrieve the data, and save it out. But, the functionality is there if you need it.

Thank you.