Exporting BCp to a Compressed File (Rar)

Question

Exporting BCp to a Compressed File (Rar)

Suth

SSCommitted

Points: 1559
More actions
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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

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!

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

Cross posted follow on question

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