Another BCP Export [Fail]ure All, in SELECT Data Set Using Stored Procedure with BCP Error @Remus Rusanu provided a great solution to the problem. However, this evolved in to another problem: this BCP query now runs 

DECLARE @SQL VARCHAR(8000); 
SELECT @SQL = 'bcp "EXEC [2BSHAEOS]..ispsSelectEmptyAsNull ''B1A'';" ' + 
               'queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_BCPTesting2.txt" ' +
               '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; 
EXEC master..xp_cmdshell @SQL;
GO


This was then failing due to the ordering of the fields in the .fmt file conflicting with those in the results from my SP. Now, following a rewrite of my SP to get the order of the fields in the ORDINAL_POSITION equal to the .fmt file, the above BCP export query gives me 

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '#TmpTableColumnNames'.
NULL

Edit: Changed SP Where the SP is 

IF EXISTS (SELECT name 
           FROM sys.procedures 
           WHERE name = N'ispsSelectEmptyAsNull') 
DROP PROCEDURE ispsSelectEmptyAsNull;
GO
CREATE PROCEDURE ispsSelectEmptyAsNull @TableName NVARCHAR(256)         
AS    
DECLARE @Columns VARCHAR(MAX);
SELECT @Columns =  
    COALESCE(@Columns + N', 
    NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '',
  N'NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '') 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @TableName 
ORDER BY ORDINAL_POSITION;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
    SELECT ' + @Columns + N' 
    FROM ' + @TableName + N';';
EXEC (@SQL);
GO


To say this stuff is making me want to poke myself in the eye with a long sharp stick is an understatment. Any help would be appreciated. Clearly from this MSDN link this error is an invalid column number. But the ORDINAL_POSITIONs of both the .fmt and the results obtained from the SP are the same!
As I wrote in the comment to your previous post:


  the bcp syntax look ok. 
  I double checked with BOL and the only limitation for bcp is that 
  if you use a procedure all tables have to exist prior to bcp being executed


No temporary tables or bcp will fail!

Update 1: 
Here is an example on how to exclude columns in the format file (*.fmt) for the bcp utility:

9.0
3
1       SQLCHAR       0       7      "\t"     1     Col1         ""
2       SQLCHAR       0       0      ""       0     Col2         ""
3       SQLCHAR       0       100    "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS


Note the zero (0) in the length for Col2. You can find this example in the BOL of SQL Server 2008 at: 

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/30e0e7b9-d131-46c7-90a4-6ccf77e3d4f3.htm 

Or search for Using a Format File to Skip a Table Column in BOL.