Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: Use bcp to output data to a compressed (zipped) folder

    Hi all,

    I am trying to use bcp to output data to a compressed (zipped) folder.
    The bcp command is called from a step in scheduled job in SQL 2005 (T-SQL) similar to:

    SET @chvCommand =
    'bcp [working_t] out D:\Eprojects\Edata\Cdata\200701.dat'
    + ' -c -STPISQL -T'

    EXECUTE master.dbo.xp_cmdshell @chvCommand, NO_OUTPUT

    .... where Cdata is a compressed (zipped) folder.
    The scheduled job seems to work without errors, but afterwards there is nothing in the compressed folder.
    If Cdata is a regular folder everything works fine.


    Thanks for you insight.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You're kidding right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    He doesn't seem to be...

    The reason the job seems to go well is because the execution of xp_cmdshell has no error. The DOS-command inside the shell will fail but the error is ofcourse not interpreted as such outside of the shell.

    Check the output of this job.

    Btw: it's clear this isn't going to work, is it?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The table that is getting exported could be in the master database....

  5. #5
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    purpose: to archive and zip massive amounts of data

    The purpose is to archive and zip massive amounts of data.

    Brett --- why would you think I was kidding?

    The code works when the folder is a regular folder.
    The exact same code does not work when the folder is a compressed (zipped) folder.

    Why is it clear this isn't going to work?

    Thanks.

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by tomstone_98
    the folder is a compressed (zipped) folder.
    When you say "zipped" I suppose you mean it's created with WinZip or something. This actually doesn't create a folder but a file. You can't BCP to a zip-file. It may appear as a folder in the Explorer but it is still a (specially formatted) file. Explorer shows it as a folder because of some clever extention that shows it like a folder to you. Outside the explorer, eg. in a command window, it is shown and behaves as an ordinary file.

    What should work however is:
    - Create a normal folder with the explorer
    - Open the properties of that folder and check the box "compressed folder"
    Files placed in that folder will be automaticly compressed for you by the OS.

    Hope this helps.

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you can also invoke most compression apps from the cmd line, so if this is part of a batch process you could add a line to your .bat that would add the file to your zipped up folder, after the output file is created by bcp in a (real) folder.

    EDIT: i see you are invoking bcp from xp_cmdshell. In that case you could just as easily invoke winrar.exe or similar from xp_cmdshell to zip up your bcp file.
    Last edited by jezemine; 01-25-07 at 19:25.

  8. #8
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    Thank you lexiflex ... your advice worked.

  9. #9
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Glad to be of help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •