...was recently asked at SQLTeam

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68461

This is what I would do

USE Northwind
GO

CREATE VIEW EXPORT_ORDERS
AS
SELECT 1 AS ROW_ORDER,
  'HEADER '
 + CONVERT(char(25),GetDate()) AS Data_Line
UNION ALL
SELECT 2 AS ROW_ORDER,
   COALESCE(CONVERT(char(15),OrderID),'')
 + COALESCE(CustomerID,'')
 + COALESCE(CONVERT(char(15),EmployeeID),'')
 + COALESCE(CONVERT(char(25),OrderDate),'')
 + COALESCE(CONVERT(char(25),RequiredDate),'')
 + COALESCE(CONVERT(char(25),ShippedDate),'')
 + COALESCE(CONVERT(char(15),ShipVia),'')
 + COALESCE(CONVERT(char(15),Freight),'')
 + COALESCE(CONVERT(char(80),ShipName),'')
 + COALESCE(CONVERT(char(120),ShipAddress),'')
 + COALESCE(CONVERT(char(30),ShipCity),'')
 + COALESCE(CONVERT(char(30),ShipRegion),'')
 + COALESCE(CONVERT(char(20),ShipPostalCode),'')
 + COALESCE(CONVERT(char(30),ShipCountry),'') AS Data_Line
  FROM Orders
UNION ALL
SELECT 3 AS ROW_ORDER,
  'TRAILER '
 + CONVERT(char(25),GetDate())
 + CONVERT(char(15),COUNT(*)) AS Data_Line
  FROM Orders
GO

SELECT Data_Line FROM EXPORT_ORDERS ORDER BY ROW_ORDER

EXEC master..xp_cmdshell 'bcp Northwind.dbo.EXPORT_ORDERS out C:\Orders.txt -S<servername> -c -T'
GO

DROP VIEW EXPORT_ORDERS
GO