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!
