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:
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;
Asked in February 2016
Viewed 1,958 times
Answered 1 times
Leave an answer
Quote of the day: live life