BCP out a table

if object_Id( 'dbsp_bcp_out') Is Not Null
 drop procedure dbsp_bcp_out
go

CREATE procedure dbsp_bcp_out
@tbl varchar( 40) = NULL,
@db varchar( 30) = NULL,
@path varchar( 200) = 'c:\'
as
/*
*************************************************************
Name: dbsp_bcp_out
Description:
   BCPs out an entire table to a local file on the server. The
user passes in the name of a table or view, the database in which
this is located and (optionally) the path to write the text file
to. The file is written out as: <table>.txt

Usage: exec dbsp_bcp_out <tbl>, <db>, <path>
exec dbsp_bcp_out 'producttype', 'iqd_dev', 'c:\mssql'

Author: Steve Jones (7-26-1999)
Copyright: 1999 dkRanch.net

Input Params:
-------------
@tblName of a table or view to bcp out of the server
@dbName of the database in which the table is located.
@pathpath in which to write output file.

Output Params:
--------------

Return: 0 if no error.

Results:
---------

Calls: master..xp_cmdshell

Locals:
--------
@errHolds error value
@cmd          Holds BCP command 

Modifications:
--------------

*************************************************************
*/set nocount on
declare @err int,
 @cmd varchar( 250)

select @err = 0
/*
Check parameters and exit if not correct.
*/if @tbl Is NULL
 select @err = -1
if @db Is NULL
 select @err = -1
if @err = -1
 begin
  Raiserror( 'Parameter Error:Usage:exec dbsp_bcp_out <tbl>, <path>', 12, 1)
  return @err
 end
if ( select count(*) from sysobjects where name = @tbl) = 0  
 begin   
  Raiserror( 'Table does not exist;Please pass in the name of an existing table.', 12, 1)
  return  -1
 end  
if ( select count(*) from master..sysdatabases where name = @db) = 0  
 begin   
  Raiserror( 'Database does not exist;Please pass in the name of an existing database.', 12, 1)
  return  -1
 end  


/*    Ensure there is a backslash on the path */if right( rtrim( @path), 1) != '\'
  select @path = rtrim( @path) + '\'  


/*
Build the BCP command string
*/select @cmd = 'bcp ' + rtrim( @db) + '..' + rtrim( @tbl) + ' out ' + rtrim( @path) + rtrim( @tbl) + '.txt -n -S' + @@servername + ' -U sa -T -E'  
select @cmd = 'master..xp_cmdshell ''' + rtrim( @cmd) + ''''

/*
Run the BCP command to move the data out.
*/exec( @cmd)  
select @cmd
return @err
GO
if object_id( 'dbsp_bcp_out') Is Null
 select 'dbsp_bcp_out Not Created'
else
 select 'dbsp_bcp_out Created'
go