Back to the subject... which was, how to find errors when running BCP from xp_cmdshell...

You can run into trouble if you are trying to nest the INSERT...EXEC logic. Run...

CREATE PROCEDURE p_foo3

AS

CREATE TABLE #foodata3 (abc CHAR (1))

INSERT INTO #foodata3 (abc)

SELECT 'A'

SELECT *

FROM #foodata3

GO

EXEC p_foo3

GO

abc

---

A

CREATE PROCEDURE p_foo2

AS

CREATE TABLE #foodata2 (abc CHAR (1))

INSERT INTO #foodata2 (abc)

EXEC p_foo3

SELECT *

FROM #foodata2

GO

EXEC p_foo2

GO

abc

---

A

CREATE PROCEDURE p_foo1

AS

CREATE TABLE #foodata1 (abc CHAR (1))

INSERT INTO #foodata1 (abc)

EXEC p_foo2

SELECT *

FROM #foodata1

GO

EXEC p_foo1

GO

Msg 8164, Level 16, State 1, Procedure p_foo2, Line 5

An INSERT EXEC statement cannot be nested.

(0 row(s) affected)

(0 row(s) affected)

If you have a procedure that moves data via BCP and you insert the output into a table, and then you call the procedure from another procedure and try capturing the output in the same manner, it doesn't work.

I'd always considered this behavior of SQL Server's as an arbitrary limitation they would someday fix. But apparently not. Too busy adding features we don't need to fix a a shortcoming in a feature we might actually use?