I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

Thanks all

You can use the extended stored procedure xp_cmdshell to execute bcp from a
stored procedure. Please read in SQL Server Books Online the security
implications. Assuming you do not want to run it under an account that is
member of sysadmin, you may want to set up a proxy account via
sp_xp_cmdshell_proxy.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Mike (mckeyes [at] gmail.com) writes:
> I'm using SQL 2005 to export data. I would like to use the bcp utility
> to export data to an Excel file.
> I have to generate quite a few files and the names are dynamic. The
> ideal would be to loop through records in a stored procedure to create
> a file name to use in the bcp. My question is how can I use the bcp
> from a stored procedure? I know how to run it from the command prompt.
> Is there a way to control the command prompt from a stored procedure?

As Plamen said, you can use xp_cmdshell, but xp_cmdshell is a security
risk and for this reason it is disabled by default. It may be better
to write a small application VBscript or whatever you fancy to run
the export.


--
Erland Sommarskog, SQL Server MVP, esquel [at] sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx