Can I use bulk copy program (bcp) to copy the result set of a stored procedure to a file?
Absolutely. Imagine that you've defined a procedure in Northwind called BcpOutOrders. The following command copies the data to a text file called authors.txt:
Bcp "EXEC northwind..BcpOutOrders"
QUERYOUT authors.txt —Usa —Ppassword
—Sserver —c
The trick is using the QUERYOUT keyword rather than the usual OUT keyword that you use when you reference a table or view. To avoid frustration, keep two things in mind. First, if the procedure returns multiple result sets, the QUERYOUT statement copies only the first result set to the data file. Second, the bcp utility is particular about how you use quotation marks. SQL Server Books Online (BOL) contains details about how to punctuate your code; you need to use double quotes around a query or procedure name and single quotes for items embedded in the query.