This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 89%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EC%3C/text%3E%3C/svg%3E)
I am running the following command but i am getting an error:
bcp "select * from (select [Style Code] as [Style Code],[MY Code] as [MY Code] union select [Style Code],[MY Code] from UnPivoted_Table)q order by case [Style Code] when [Style Code] then 0 ELSE 1 END" queryout "\\server.domain.com\CSV_Files\file1.csv" -c -t, -T -S "server1.db.com" -d "DB1"
error:
Starting copy...
SQLState = S0022, NativeError = 207
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'Style Code'.
SQLState = S0022, NativeError = 207
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'MY Code'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations
I attempted to put double single quotes around the column names, but thats resulted in error as well:
Starting copy...
SQLState = 37000, NativeError = 102
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ''.
SQLState = 37000, NativeError = 102
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ')'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations
I am following the answer from here because im trying to export the column headers as well to the csv file (otherwise only the data is getting exported if i dont specify the columns in the bcp cmndlet)
I think the error has something to do with the column formatting, because the original answer has non-spaced columns without brackets, but since my columns have spaces in the name, i need to use bracket, but then how do i resolve this error?
Did you also try the next command?
bcp "select * from (select 'Style Code' as [Style Code], 'MY Code' as [MY Code] union select [Style Code], [MY Code] from UnPivoted_Table) q order by case [Style Code] when 'Style Code' then 0 ELSE 1 END" queryout "\\server.domain.com\CSV_Files\file1.csv" -c -t, -T -S "server1.db.com" -d "DB1"
@Viorel OMG that worked!! but now im getting this error:
Starting copy...
SQLState = 37000, NativeError = 8114
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Error converting data type varchar to numeric.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations
Now I think i know why this is happening, because one of the columns is a decimal datatype. but what can i do in this case then?
I think that you should cast the numeric columns in this part of the query: “…union select cast([Style Code] as varchar(max)), cast([MY Code] as varchar(max)) from UnPivoted_Table) q…”.
@Viorel that does the trick indeed! However, what if i want to load the csv file back into a tale with numeric (decimal) datatype columns...wouldn't that result in error? I remember I ran into these kinda issues before when i was developing a sql bulk copy script to load huge csv files into sql tables.
Im afraid i'd run into an error like this:
Exception calling "WriteToServer" with "1" argument(s): "The given value of type String from the data source cannot be converted to type real of the specified target column."
so i wonder if theres a way to make the bcp work without having to cast the columns to varchar and instead preserving their data types as is...? or is bcp limited only to varchar export?
It seems that exporting to CSV with headers already works (although, there are alternatives) and you are considering the re-import operations with BCP. To skip the first header row in import operation, you can specify the “-F 2” option.
is bcp limited only to varchar export?
No, you can export data in native (binary) format. But in that case it does not make sense to add headers.
What is the purpose of your file? If the purpose is to copy the data to another SQL Server instance, native format is the best choice. And there is no reason to have headers.
But if you are exporting data to another system, maybe outside your organisation, which may not be running SQL Server, you typically use character format, varchar or nvarchar. And in this case, the receiver of the file may for one reason or another want to have headers.
So if you are creating a text file with BCP, it does not really matter if you convert in the query, or if you let BCP do the job. The result is likely to be the same. However, it's messier to do it yourself in T-SQL.
@Viorel hmm, so after running the bcp command with the casting, i didnt see the headers...but further research thanks to @Erland Sommarskog website article here seems to suggest that the header will not be necessarily ordered as the first row. I can confirm this because I tried opening the exported file, which by the way is SO huge (1.2 GB) to the point notepad++ wasnt even able to open it, and excel limited how many rows it could display!
Anyhow the article goes on to suggest a way with format files to circumvent the need of UNION and make the header the first row.
The question is: With the Union query we have here, can we ensure the header be the 1st row?
@Erland Sommarskog
all i want to do is export the data from the view so i can reimport it into a table. the table will have the same data type, the same columns, the same structure.
Its all in the same SQL database/instance.
So i suppose my best option is to just bcp as native format, theerfore i tried the following export command, which worked, but when i tried reimporting the file, i got an error:
Export:
bcp DB1.dbo.UnPivoted_View OUT "\\server.domain.com\Scripts\file4.bcp" -T -S "server1.db.com" -n
Import:
bcp DB1.dbo.Table1 IN "\\server.domain.com\Scripts\file4.bcp" -T -S "server1.db.com" -n
but now i got an error on import:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file
If [Style Code] is a numeric column or never contains ‘Style Code’ value, then the command should work, I think. It is not clear what query did you try. You can also consider this query:
select [Style Code], [MY Code] from (select 'Style Code' as [Style Code], 'MY Code' as [MY Code], 1 as n union select cast([Style Code] as varchar(max)), cast([MY Code] as varchar(max)), 2 from UnPivoted_Table) q order by n
You can also try it in Management Studio.
If it is imperative to export the data to text file with headers, you can also try this approach: export the data without UNION, without headers and without casting, then execute the next command from Command Prompt:
copy h.csv+file1.csv file2.csv
where h.csv is a file that contains header line only (edited manually).
Just repeating what I said in another thread: there is an article on my web site where you can learn how to export a file with headers without using UNION: http://www.sommarskog.se/bulkload.html
So there is a problem in the forum with this thread. I have received an email notification that Cataster has made a comment, and I can see that there are 8 comments to Viorel's answer, but the forum only displays one. The email notification only has text, so I may be missing any graphics.
all i want to do is export the data from the view so i can reimport it into a table. the table will have the same data type, the same columns, the same structure. Its all in the same SQL database/instance.
In that case, you should use native format and you should not bother about column headers. As you may have realised by now, adding headers with BCP is an exercise on the more advanced side.
When exporting for data for this purpose, you should use native format (since a conversion to text format could cause loss of precision in some cases). You should also use a format file, but don't worry, because it is easy:
bcp mydb.dbo.myview format null -f myview.fmt -n -T -S server\instance
bcp mydb.dbo.myiew out myview.bcp -f myview.fmt -T -S server\instance
That is, you use BCP to generate a format file. Why you should use a format file? This is something I learned the hard way... (There can be subtle differences between source and target which the format file can sort out.)
If you get the error Unable to open BCP host data-file when you trying to import the data, that most likely means that the file you specify does not exist.
@Erland Sommarskog I also ran into the same issue, on mobile, but on desktop i had to click "show more" to expand the comments.
anyways, thanks for the information regarding the format file!
I ran the commands and im getting this when running the 2nd command:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Incorrect host-column number found in BCP format-file
, but on desktop i had to click "show more" to expand the comments.
I know about that one. :-) In this case, there no such thing and when I clicked the notification mail I only saw the top. Right now, I see all comments, though.
Incorrect host-column number found in BCP format-file
You're telling me that you first created the format file with bcp format, and when you use it with bcp out you get this error? I would double-check that the data source is the same in both cases..
@Erland Sommarskog yep i double checked and the data source is the same...still getting the error though
this is how the generated .fmt file looks like:
1 SQLCHAR 2 100 "" 1 Style Code SQL_Latin1_General_CP1_CI_AS
2 SQLDECIMAL 1 19 "" 2 MY Code ""
BCP is a tool that was born in the 1980s and it is showing its age. It has a few rough edges. You need to edit the format tile and remove the space in the column names. There is no problem with editing the names, because the names are not really used.
@Erland Sommarskog legacy software lol, its one of those things we gotta deal with.
so anyways i removed the spacing in the column names,
so that its like this now:
1 SQLCHAR 2 100 "" 1 StyleCode SQL_Latin1_General_CP1_CI_AS
2 SQLDECIMAL 1 19 "" 2 MYCode ""
and still got the same error
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Incorrect host-column number found in BCP format-file
To export using native types, try this command too:
bcp mydb.dbo.myview out myview.bcp -n -T -S server\instance
Can you upload the definition of the table you are trying to import to, the format file and the data file somewhere? This forum accepts attachments, but only of a limited types of file. I don't think you can attach zip files, but I may be wrong. If you can't attach here upload to Dropbox, OneDrive or similar.
When working with BCP it is important to see the exact bytes, because the tool is very squared.
@Viorel tried that, it exported (tho its a very large 1.5 gb file for some reason), and when i tried importing it using IN instead of out, i got this error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
@Erland Sommarskog I think i found the solution! according to this post, dlnewman70 said that he had to put an underscore in the fieldnames (he had another post where he stated he removed the fieldnames to make it work, but when i did that i got an error
"I/O error while reading BCP format file"
)
so i reverted that change and instead replaced the spaces with underscores, the bulk copy to host-file started working :)
another reference to the underscore answer also here by steve smith
Hi @Cataster ,
Welcome to Microsoft Q&A.
If you already find the solution, it is recommended for you to post an answer with this solution and accept it as answer or accept the other answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!
Best regards
Melissa
@Erland Sommarskog I think i found the solution! according to this post, dlnewman70 said that he had to put an underscore in the fieldnames
Underscore or just deleting the space should be the same. The problem is the space, since BCP splits the line in format file on space.
Anyway, if you got working, let's consider this a happy ending.
@MelissaMa-MSFT hi Melissa
Thanks for the warm welcome. I accepted the answer, although this was a bit difficult because the answer turned out to be different for changing requirements. however, for the context of this question, I accepted the relevant answer accordingly