Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
+1, I had taken this out of context in my own mind! Thanks, but how the hell do I get a self contain SP that will allow me to build the correctly order field list if not with TmpTables? Man, this stuff drives me round the bend... Again, thanks for your time. –  Killercam Nov 20 '12 at 15:37
    
I have ammended the stored proceedure as above. But I am still getting an error that is documanted here connect.microsoft.com/sqlserver/feedback/details/584001/bcp can you offer any ideas as to a resolution? Thanks again. –  Killercam Nov 20 '12 at 15:50
1  
I think you can output all the fields but shorten them to zero length. I'd have to dig around in my bcp files, but that was (I recall) the solution to a similar problem I encountered with SQL Server 7.0 –  hot2use Nov 20 '12 at 15:56
1  
@Killercam, could you use global temp tables (e.g. ##TmpTableColumnNames)? –  Eli Nov 20 '12 at 22:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.