bcp utility

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 30 Nov 2007 16:34 GMT

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

Reply to this Message

Plamen Ratchev - 30 Nov 2007 19:00 GMT

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

Reply to this Message

Erland Sommarskog - 30 Nov 2007 22:10 GMT

> I'm using SQL 2005 to export data. I would like to use the bcp utility
> to export data to an Excel file.
[quoted text clipped - 3 lines]
> 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.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Reply to this Message