Today’s post is about a real world use case scenario. In our story, there is a 3-tier application that uses a SQL server as a backend. The developer is tasked to provide ability that copies certain customer information from the database to the text file on a daily basis.
There are multiple solutions to this problem. The most obvious one would be to create a small utility that would connect to the database, extract the data using ADO.NET, and then write the data to the text file. The windows task scheduler would take care of the daily scheduling. There is nothing wrong with this approach except, that it is not the quickest and the least error prone one.

Alternatively, we could tap into the power of SQL server to perform the entire job for us without any external application interaction. SQL server comes with a utility called bcp whose sole purpose is to copy the data between a database and a file in a user-specified format. bcp can copy a table, view, or a SQL query result set.
Lets go back to our example where we needed to copy the customer data from the database to the text file. We are going to attack this problem by creating a store procedure:

-- Configure the databse for output
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE    
declare @sql varchar(100)
select @sql = 'bcp "SELECT * from Northwind.dbo.Customers where BirthDate > 1/1/1980" queryout "C:CustomerInfo.txt" -w -T -S'+ @@servername
EXEC xp_cmdshell @sql
-- Return the database configuration back to its original state
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE

On the first five lines (1-5) we are preparing the database for the data output . The real magic starts on the line number 7. Here we are calling the bcp utility with few arguments.
The first argument (SELECT * from Northwind.dbo.Customers where BirthDate > 1/1/1980) is a query that returns all customers who were born after 1/1/1980. Note the syntax for the table name. It is prefixed by the table owner (dbo) which is prefixed by the database name (Northwind).
The second argument (queryout) is telling the bcp to bulk copy the data.
The third argument (C:CustomerInfo.txt) is the file and folder location of the output file. The folder must exist, but file does not. If it does, it will be overwritten.
The -w argument tells bcp to perform the bulk copy using database data types of the data for noncharacter data, and Unicode characters for character data.
The -T argument tells the bcp to connect using secure trusted connection of the network user. Therefore, it is not needed to specify user credentials.
The -S argument stands for server name and it must be followed by the server name. In our case it is @@servername system variable. @@servername returns the name of the local server running the SQL server.
On the line 8 we spawn a windows command shell passing it our string that will invoke the bcp with its arguments.
Once the bcp has executed, we return the database state back to its original state as described on lines 1- through 13.
The bcp utility has many more options that are documented here.

With only few lines we were able to accomplish our task of copying data from the database to a file. There is only one item that we have not addressed yet. The process needs to be run daily. We can accomplish this by creating a new job in SQL Server Agent.

This will open New Job dialog that contains several pages that may be configured. The Steps and the Schedule are the only relevant to us. Since the Schedule is self explanatory, we will skip its details. The Steps is the page we are more interested in. Every job can have multiple steps. Our example is simple and therefore has only one step. We are going to create a new step by clicking the New button on the Steps page to open a new step dialog. Choose Transact-SQL script (T-SQL) for the Type. In a Database drop down list, select the appropriate database. The Command field needs to have the call to our store procedure that we have created above.

At this point the Steps page is complete.
Our job is also complete provided we have already configured the job schedule.

Conclusion
SQL server is a powerful application that can save hours of development time. Copying data to a file is a prime example of it. Next time you need to perform some data manipulation, think about doing it in the SQL server. If not sure if it can be done, talk your DBA.