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.
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