0

I am facing the problem with BCP (bulk-copy program) on MSSQL-Server 13.0. Here it goes:

I am trying to export data with that program but file is always corrupted. I am exporting a .zip file. But it works perfect on MSSQL-Server 11.0 - the same command can export any zip file that I need to export.

Command that I am using to export:

bcp "select query" queryout "C:\export\myzip.zip" -T -S SERVER_NAME

Tried to use -V flag, but it didnt work.

Edit 1: After command is used BCP shows me questions about prefix, format and etc - I am using: I 0 0 (space) n

Edit 2: The starting bytes of the exported file are: 37 7a bc af 27 1c

TT.
  • 15,774
  • 6
  • 47
  • 88
vovka15
  • 3
  • 2
  • `but file is always corrupted` are you getting any error – TheGameiswar Aug 18 '17 at 08:59
  • I dont get any errors while exporting my data. But when I am trying to open exported data it shows me: Windows cannot open the folder. The compressed (zipped) Folder "C:\export\myzip.zip" is invalid – vovka15 Aug 18 '17 at 09:17
  • You should be using a format file which specifies 0 as prefix length. [Example](https://stackoverflow.com/a/40860039/243373). – TT. Aug 18 '17 at 09:19
  • I am using it. Edited main question. – vovka15 Aug 18 '17 at 09:21
  • What are the first few bytes of the exported file? – TT. Aug 18 '17 at 09:31
  • @vovka15, you need to specify the `-c` BCP argument to create a text file without prompting for file format specifications. That will create a tab-delimited file with cr-lf record terminators. Simply specifying ".zip" as the file extension will not automatically compress the file; you'll need to compress separately. – Dan Guzman Aug 18 '17 at 10:19
  • 37 7a bc af 27 1c 00 03 – vovka15 Aug 18 '17 at 10:20
  • @DanGuzman How it should help? I cannot understand. If I won't specify file format when I am trying to extract it will be created las a dump file. If I compress that file it will be .zip with that dumpfile into. – vovka15 Aug 18 '17 at 10:33
  • @vovka15, no format file is required with `-c`. That will create a text file with the file format I described that can be read using any text editor. – Dan Guzman Aug 18 '17 at 10:36
  • @DanGuzman, sorry, still cannot get it. I have .zip file with many files into it. If I create text file what will be next step to get my zip. You said that it can be read using any text editor. But for what? – vovka15 Aug 18 '17 at 10:41
  • BCP does not create compressed files directly. You'll need to first create the text files with BCP and then compress all those text files into a single compressed zip file. The latter can be done in Powershell. See https://stackoverflow.com/questions/17546016/how-can-you-zip-or-unzip-from-the-command-prompt-using-only-windows-built-in-ca. – Dan Guzman Aug 18 '17 at 10:51
  • Looking [here](https://www.filesignatures.net/index.php?page=search&search=377ABCAF271C&mode=SIG) for the file signature, it appears to be a 7-zip file format. So AFAICT the file was exported correctly. The extension is `.7z` though, not `.zip`. You can see that [here](https://en.wikipedia.org/wiki/List_of_file_signatures) if you search for the starting bytes `37 7a bc af 27 1c`. – TT. Aug 18 '17 at 11:54
  • @TT. Thank you! It really was in .7z format. – vovka15 Aug 18 '17 at 14:03

1 Answers1

0

Looking here for the file signature, it appears to be a 7-zip file format. So AFAICT the file was exported correctly. The extension is .7z though, not .zip.

You can see that here if you search for the starting bytes 37 7a bc af 27 1c.

TT.
  • 15,774
  • 6
  • 47
  • 88