none
BCP host-files must contain at least one column

    Question

  • Hi,

    I need to export the output of a stored procedure to file. So I am writing a bcp command as 'bcp "exec myproc" queryout "outputfile" -S server -U user -P pwd -n -t, -r "\r\n" '.

    But I am getting an error 'BCP host-files must contain at least one column'. I am getting this error even if I use a format file. Do u have any idea of what I am missing in it?

    Thanks.

    Friday, July 07, 2006 3:47 PM

All replies

  • 1. Is your stored procedure returning any data? This error would occur of the stored procedure does not return any columns (this is not the same as an empty result set). Something like:

    CREATE PROCEDURE myproc

    AS

    --No SELECT statement in procedure

    --OR the SELECT statement is within an IF

    --and it does not run because of a false condition

     Check your stored procedure in Query Analyzer.

     2. By the way, why is there a comma between the row and column terminator switches ( -n -t, -r "\r\n")?

     

     

    Monday, July 10, 2006 10:05 PM
  • Hi Nathan,

    Thx for your reply. My stored procedure is having a 'select' stmt at the end of the procedure. In fact when I use 'osql' to execute the procedure and redirected the output to a file, I got the output. But since 'osql' is adding space after the data if it is less than the specified length, the output more or less becomes a fixed length file. But considering my filesize, I want it to be a comma delimited file. That's why I am trying to use 'bcp'.

    The comma is to direct bcp to use ',' as column delimiter rather than the default value.

    Thx.

    Tuesday, July 11, 2006 3:42 PM
  • Go to Query Analyzer and run this:

    SET FMTONLY ON
    exec myproc
    SET FMTONLY OFF

    If you get an empty result set with a list of the columns then your stored procedure can actually be used with the BCP utility. Otherwise, a few changes may have to be made to the stored procedure

    Wednesday, July 12, 2006 2:04 PM
  • Hi,

    I ran the above batch and I got an empty result set with column names.

    Thx.

    Wednesday, July 12, 2006 3:20 PM
  • I do get an empty resultset with two column names however bcp stil fails.

     

    Thoughts thanks

    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column

     

     

    FYI

     

    BCP - Bulk Copy Program for Microsoft SQL Server.
    Copyright (c) 1991-1998, Microsoft Corp. All Rights Reserved.
    Version: 9.00.1399.06
    Friday, November 07, 2008 4:00 PM
  • Try setting "SET NOCOUNT ON" at the start of your Stored Proc.

    CREATE PROC storedproc
    AS
    SET NOCOUNT ON
    .....


    Also remove any PRINT statements from the proc.  The messages can sometimes mess up bcp.

    Monday, November 10, 2008 2:12 PM
    Moderator
  •  

    Thanks I have that in all my sp's since ADO doesn't work correctly w/o it. But that's a different issue.

     

    Example: This code doesn't work even when I replace #tables with real tables.

     

    --create the table outside the sp

    --create table WBCP (f1 char(10), f2 char(10))

     

    create proc dbo.Weird_bcp

    as

    begin

    SET NOCOUNT ON

    truncate table WBCP

    insert into WBCP values ('A','B')

    select * from WBCP

    end

     

    -------------------------------------------------

    Exec in sql-window

     

    exec Weird_bcp

     

    Results:

    f1 f2

    ---------- ----------

    A B

     

     

    Call via BCP

     

    bcp "exec YourDB.dbo.Weird_bcp" queryout weird.dat -S(local) -T -c

     

     

    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column

     

     

    Very frustrating

     

     

    I:\>bcp -v
    BCP - Bulk Copy Program for Microsoft SQL Server.
    Copyright (c) 1991-1998, Microsoft Corp. All Rights Reserved.
    Version: 9.00.1399.06

    Microsoft SQL Server Yukon - 9.00.3152

    Wednesday, November 12, 2008 7:10 PM