Post reply

Exporting BCp to a Compressed File (Rar)

  • November 2, 2015 at 6:40 am

    #312737

    Hi ,

    i have this bcp

    EXEC xp_cmdshell 'bcp "SELECT top 10 * FROM [db].dbo.[u_activity]" queryout "C:\bcp\Customers.csv" -c -b 10000 -t~ -S 10.20.8.149 -U user-P password'

    which throws the data into a csv, but the issue is I also need to compress the file down in a .rar file,

    can this be done in a SP while still executing the above bcp ?

    Help will be appreciated

  • November 2, 2015 at 7:30 am

    #1837081

    after the bcp is created, you can call 7za.exe(the command line version of 7zip) to zip the file.

    if you call it from xp_cmdshell, you are limited to one set of double quotes, but if you use a SQL job, you will not encounter that restriction, i believe. whitespace in a file path will screw up anything not in double quotes, so one of your paths needs to be space-free.

    the path to 7za must be accessible to the account SQL is running under, so if ti's on a network share, you'd need a domain user.

    also remember all the directories are relative to the server

    syntax example:

    --http://www.7-zip.org/download.html

    --http://downloads.sourceforge.net/sevenzip/7za920.zip

    --xp_cmdshell is limited to ONE set of double quotes.

    --one of the paths needs to not contain spaces!

    --see 2003 MS KB http://support.microsoft.com/kb/266224

    DECLARE @results TABLE(results varchar(255))

    declare @command varchar(2000)

    --zip one file

    SET @command =

    '"C:\DataFiles\7zip_CommandLine_7za465\' --path to 7za command line utility note the dbl quotes for long file names!

    + '7za.exe"' --the exe: i'm using in the command line utility.

    + ' a ' --the Add command: add to zip file:

    + 'C:\DataFiles\' --path for zip

    + 'myZipFile.zip' --zip file name, note via xp_cmdshell only one pair of dbl quotes allowed names!

    + ' ' --whitespace between zip file and file to add

    + 'C:\DataFiles\' --path for the files to add

    + 'SandBox_2011-07-25.bak' --the file

    + ' -y' --suppress any dialogs by answering yes to any and all prompts

    print @command

    --"C:\DataFiles\7zip_CommandLine_7za465\7za.exe" a C:\DataFiles\myZipFile.zip C:\DataFiles\SandBox_2011-07-25.bak -y

    insert into @results

    exec xp_cmdshell @command

    select * from @results

    /*

    NULL

    7-Zip (A) 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03

    NULL

    Scanning

    NULL

    Updating archive C:\DataFiles\myZipFile.zip

    NULL

    Compressing SandBox_2011-07-25.bak

    NULL

    Everything is Ok

    NULL

    */

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 2, 2015 at 7:32 am

    #1837082

    Cross posted follow on question

    http://qa.sqlservercentral.com/Forums/Topic1732853-391-1.aspx

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply