Microsoft SQL BCP Format File - Version?

bcp sql sqlbulkcopy sql-server-2012 sql-server-2014

Question

I have a question regarding Microsoft SQL's Bulk Copy Program (BCP.exe). I am relatively new to using the program so am hoping it's an easy question for some, however I am stuck!

On Server #1 we're running Microsoft SQL 2014, it runs the BCP to export a number of tables. It provides me with a *.DAT and *.FMT file per table.

When I go to import this data on to Server #2 running Microsoft SQL 2012 I receive an error:

SQLState = HY000, NativeError = 0Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Syntax error at line 2 column 0 in xml format file. | Exit Code: 1

I read there were versioning issues using BCP going from a newer to an older version of SQL, and quickly found a way to manually fix this.

In the FMT file as you may know the very first line contains the SQL version number that exported the data - which in this case is 12.0. If I change this to 11.0 (or 10.0), then re-run the import everything works fine & dandy!

Here is an example of the FMT file before I edit the first line:

12.0
6
1       SQLNCHAR            2       72      ""   1     ListID                                                                       SQL_Latin1_General_CP1_CI_AS
2       SQLNCHAR            2       32      ""   2     EditSequence                                                                 SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR            2       62      ""   3     Name                                                                         SQL_Latin1_General_CP1_CI_AS
4       SQLBIT              1       1       ""   4     IsActive                                                                     ""
5       SQLNCHAR            2       30      ""   5     PriceLevelType                                                               SQL_Latin1_General_CP1_CI_AS
6       SQLDECIMAL          1       19      ""   6     PriceLevelFixedPercentage                                          

While I can fix this manually I need to automate this system to run very regularly so this is not a long term solution.Please help! Is there anything I can do?

I either need to fix the export so it uses v11.0 or older, or fix the import so it recognizes v12.0. I have tried using the -V flag on both the import & export however this doesn't make a difference on either end.

Thanks in advance for your expert advice.

Popular Answer

I managed to solve this problem by installing Microsoft® Command Line Utilities 11 for SQL Server® and adding the bcp.exe location to the path variables. Now I can export version 11 using bcp 11, thus making the exported format files compatible with SQL 2012. You can download it from here https://www.microsoft.com/en-us/download/details.aspx?id=36433



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why