...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