Search code examples
sql-servert-sqlbcp

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:

9.0
3
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?

Solution

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

    SELECT @@SERVERNAME;

    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;