BCP format file version number (non-xml format line 1), what is proper number when using BCP 15?

Question

Some code that was written to create BCP format files for bulk load use that originally written using BCP 13 is now being used with BCP 15 and failing on improper version number in the format file (non-xml format line 1) ... more specifically ...

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Syntax error at line 2 column 0 in xml format file.

Someone else wrote the original code years ago, but after seeing error and reviewing the bcp and bcp format documentation pages, I'm convince that person had it wrong in there design so I'd like some clarification. More specifically ...

The original code was written to get the bcp version number by using "bcp -v" and then use that number as the format file version number (non-xml format line 1), thus the use of BCP 15 caused a 15.0 as a value and fails, but if I switch the format file version to 11.0 (what BCP 13 -v would yield) then it runs under BCP 15.

When I read the bcp and bcp format documentation pages they don't seem to say exactly what the basis of the version number should be for the format file (but show examples with various number) nor does it say it should match some other version number (like some sql server number). What they do seem to say/imply is use the bcp tool with out -f switch (for prompted mode) to develop/save your own bcp.fmt format file and use it to run other requests in batch/silent mode with the -f switch. So I ran in prompted mode and see the saved format file uses 14.0 as a version number. I re-ran the original fail with 14.0 and it ran fine.

So if I am reading the pages right (and believing my retest) ... whatever bcp uses for a version number when it creates/saves a bcp.ftm format file itself then that is the version value to use when creating other format files (and not the bcp -v value). Is this the correct interpretation or is there some chart of what values are valid? Or worst yet, was the other person right (bcp -v should be used for format file version number) which means there is a MS bug in bcp and I can't read.

Answer 1

The version number in the format file is version of the FORMAT file format version used by the BCP version.

You can use the following to generate a file and see what version it is using:

bcp table_or_view format nul -fformat_file_name  

See:

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/non-xml-format-files-sql-server?view=sql-server-ver15#Structure

Answer 2

I did see that page and found this chart ...

(excuse my edits the website is interpreting the leading number as bulleted list so I'm adding a lead "x " and block quoting to avoid).

x 9.0 = SQL Server 2005 (9.x)
x 10.0 = SQL Server 2008
x 11.0 = SQL Server 2012 (11.x)
x 12.0 = SQL Server 2014 (12.x)

confusing since you can download/install bcp separately and is unclear which bcp it is referencing plus chart doesn't go up to latest SQL Server 2019. Possibly would be clearer to me if it said something like ...

x 9.0 = SQL Server 2005 (9.x) where "bcp -v" equal ##.#
x 10.0 = SQL Server 2008 where "bcp -v" equal ##.#
x 11.0 = SQL Server 2012 (11.x) where "bcp -v" equal ##.#
x 12.0 = SQL Server 2014 (12.x) where "bcp -v" equal ##.#
(and had the latest SQL Server versions)

However, the trailing paragraph to this chart says that based on format number the target sql server can always read/interpret a format file with version number that is lower than the one generated by the sql server's own "matching" bcp tool. In order words always upward compatible but not downward so you can always use an older format file "style" provided there are no differing (ie new) field order elements that require a newer version level.

Is that the correct interpretation?

Answer 3

@Terry Schwarz ,

Generally, XML and non-XML format files are interchangeable. However, we recommend that you use the XML syntax for new format files because they provide several advantages over non-XML format files.

Useful link: create-a-bcp-format-file

I would recommend to use bcp format files in XML format. They don't contain bcp version.
For example:

<?xml version="1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"  
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
 <RECORD>  
 <FIELD ID="1" xsi:type="NCharPrefix" PREFIX_LENGTH="8"  
        COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
 </RECORD>  
 <ROW>  
 <COLUMN SOURCE="1" NAME="text" xsi:type="SQLNVARCHAR"/>  
 </ROW>  
</BCPFORMAT>  

-- UPDATE --
bcp.exe is a command line utility. It is dependent/using on ODBC driver to connect to SQL Server database. bcp is detached from the SQL Server and its versions.
bcp -v shows bcp version. it has nothing to do with SQL Server versions

Answer 4

@Yitzhak Khabinsky ... thank you for your input.

I think part of my point was that bcp -v value should not be mistaken as bcp format version and just what is a proper number to use. Based on the paragraph just under the chart that was cited, the lower the number the more likely it is to be read by older and newer servers without error ... and I'm just looking for a confirmation of this (not an alternative method like xml).

Your xml sample has a version (BCPFORMAT xmlns=), its just not some solitary decimal like a non xml uses. Maybe its value maters at runtime or maybe it doesn't (as you indirectly say), but I also kind of doubt the person that I'm trying to convince would want to rewrite as xml anyway so I'd rather not go down that road.

Answer 5

Don't listen to Yithzak. Stay away from XML format files. They are more complex with very little benefit.

Yes, it appears that BCP for 2019 generates format files with 14.0 as the version number. You could say that this is a change that was long overdue. While there has been a new version number for every release up to SQL 2017, there has not been any actual changes to the format since 8.0 (SQL 2000), which added the collation field.