Error handling can be tough when using xp_cmdshell. Before I learned the trick that I go over below, I could usually figure out in my code if an error had occured when I ran a command via xp_cmdshell. However, getting details about the error, or any output at all, was tricky and could involve parsing out files.
Before you start, please be aware that xp_cmdshell will be executed under the same security context as the SQL Server service, and can be a security problem in some environments.
This code uses the insert/execute syntax. If you've never used this before, it's a good idea to learn it. Basically, instead of inserting data into a table the normal way, you can insert the results of an execute statement - in this case the xp_cmdshell statement, like below:
Insert into XPCmdShellOutput
Execute master..xp_cmdshell 'bcp tempdb..Employee out c:\temp\Employee.txt -c'
So, below is an easy, straightforward way to use xp_cmdshell to bcp out a table, and also see the output from the command. The same principles can be used for any other use of xp_cmdshell, and not only bcp.
set nocount on
use tempdb
if object_id('tempdb..Employee') is not null drop table Employee
if object_id('tempdb..XPCmdShellOutput') is not null drop table XPCmdShellOutput
-- Create the table that we need to extract from
create table Employee (EmployeeName varchar(20))
insert into Employee values ('John')
-- This table will be used to gather the output of xp_cmdshell
create table XPCmdShellOutput (OutputLine varchar(1000))
-- Show the output of xp_cmdshell when the directory does not exist
Insert into XPCmdShellOutput
Execute master..xp_cmdshell 'bcp tempdb..Employee out c:\DirectoryDoesNotExist\Employee.txt -c'
select 'Error when directory does not exist' = OutputLine from XPCmdShellOutput
delete from XPCmdShellOutput
-- Show output of xp_cmdshell when the table to be exported does not exist
Insert into XPCmdShellOutput
Execute master..xp_cmdshell 'bcp tempdb..Employee1 out c:\temp\Employee.txt -c'
select 'Error when table does not exist' = OutputLine from XPCmdShellOutput
delete from XPCmdShellOutput
-- Finally, successfully export the table!
Insert into XPCmdShellOutput
Execute master..xp_cmdshell 'bcp tempdb..Employee out c:\temp\Employee.txt -c'
select 'Successfully export the table!' = OutputLine from XPCmdShellOutput
When you run the above code, this will be the output (note that you may need to modify the c:\temp directory in your environment, and you need create table permissions in the tempdb database):
Error when directory does not exist:
OutputLine
Password:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL
Error when table does not exist:
OutputLine
Password:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tempdb..Employee1'.
NULL
Successfully export the table!
OutputLine
Password:
NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1 Avg 1 (1000.00 rows per sec.)
NULL
The output of the xp_cmdshell was inserted into the XPCmdShellOutput table every time we ran it, because we used the insert/execute syntax. Then, we select from XPCmdShellOutput to show what the output actually was. The first section shows the error when the directory does not exist. The second shows the error message when table name to be exported is misspelled. And the third shows a successful export.
This is sample code, and simplified to make it easier to understand. In working code, you would check the XPCmdShellOutput table for the string "Error". If the error string exists, then obviously an error occurred, and the details would have been stored in the XPCmdShellOutput table. Note that when the export is successful, you can also extract other information from the XPCmdShellOutput table - for instance, how many rows were copied out, and how long the export took.
When using xp_cmdshell with bcp, keep in mind that it will NOT recognize any temporary tables that were created. If you need to use temporary tables, they must be global temporary tables, prefixed with ## instead of #.
