Home Ask Login Register

Developers Planet

Your answer is one click away!

user3671271 February 2016

Error when exeuting BCP: BCP host-files must at least contain one column

I want to use BCP in my database. For that purpose I wrote this query:

EXEC xp_cmdshell 'bcp "SELECT rtrim(ltrim(anumber)),rtrim(ltrim(bnumber)),rtrim(ltrim(duration))  FROM [CDRDB].[dbo].[CDRTABLE]"  queryout d:\myOUT.txt -S . -UCDRLOGIN -Pbeh1368421 -f "d:\myFORMAT.fmt"  '

The format file myFormat.fmt detail is this:

1 SQLNCHAR 0 5 "," 1 ANUMBER "" 
2 SQLNCHAR 0 10 "," 2 BNUMBER ""
3 SQLNCHAR 0 10 "\r\n" 3 DURATION ""

When I run the BCP command I get this error:

enter image description here

How can I solve this problem?


TT. February 2016

You have a -S . switch. I don't think that's enough for SQL Server. Execute the following in SSMS:


And use the output of this query instead of the ..

Second, is there really a need for the format file? You could supply following switches instead: -w -t, -r\r\n

DECLARE @stmt VARCHAR(8000)='bcp "SELECT rtrim(ltrim(anumber)),rtrim(ltrim(bnumber)),rtrim(ltrim(duration)) FROM [CDRDB].[dbo].[CDRTABLE]" queryout "d:\myOUT.txt" -UCDRLOGIN -Pbeh1368421 -w -t, -r\r\n -S ' + @@SERVERNAME;
EXEC master.sys.xp_cmdshell @stmt;

Post Status

Asked in February 2016
Viewed 1,958 times
Voted 12
Answered 1 times


Leave an answer

Quote of the day: live life