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.

Answer 1

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")?

 

 

Answer 2

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.

Answer 3

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

Answer 4

Hi,

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

Thx.

Answer 5

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

Answer 6

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.

Answer 7

 

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