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

Terry Schwarz 1 Reputation point
2021-01-26T05:42:16.837+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,742 questions
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-01-27T08:43:51.47+00:00

    Hi @Terry Schwarz ,

    The version of the bcp utility (Bcp.exe) used to read a format file must be the same as, or later than the version used to create the format file. For example, SQL Server 2012 (11.x) bcp can read a version 10.0 format file, which is generated by SQL Server 2008 bcp, but SQL Server 2008 bcp cannot read a version 11.0 format file, which is generated by SQL Server 2012 (11.x) bcp. Refer to Create a Format File.

    -V(80 | 90 | 100 | 110 | 120 | 130)
    Performs the bulk-copy operation using data types from an earlier version of SQL Server. This option does not prompt for each field; it uses the default values.

    80 = SQL Server 2000 (8.x)

    90 = SQL Server 2005 (9.x)

    100 = SQL Server 2008 and SQL Server 2008 R2

    110 = SQL Server 2012 (11.x)

    120 = SQL Server 2014 (12.x)

    130 = SQL Server 2016 (13.x)

    Quote from an old thread, if we do not put any -V option you should able to import it in any version.

    If I misunderstood your issue, please let me know.

    Best regards,
    Cathy

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.