Zipping a txt file

Thanks Ben, I tried this as well but same thing happens. It just keeps executing query without any result or any error.

Some of the SQL Forum say, it could be permission issue but as per my knowledge I have full access so can’t understand which permission is missing.

Varsha

Hi Varsha,

Good day.

My winzip is expired, so I cannot do the test for you,

but based on the web link http://www.memecode.com/docs/winzip.html

I think the syntax should be:

EXEC master..xp_cmdshell 'T:\Source\WinZip\winzip32.exe -o T:\Source\sso_pacingdata.zip T:\Source\sso_pacingdata.txt'

you should tell sql what to run: winzip32.exe

what is the option:  -o

what is target file: sso_pacingdata.zip

what is source file: sso_pacingdata.tx

have fun.

Ben

Hi

I'm trying to zip a Text file in SQL but once I run the command, sql keeps executing for long time and I don't get any error.

EXEC master..xp_cmdshell 'T:\Source\WinZip\winzip32.exe T:\Source\sso_pacingdata.zip T:\Source\sso_pacingdata.txt'

I also tried,

EXEC master..xp_cmdshell 'T:\source\WinZip\winzip32.exe -o T:\Source\sso_pacingdata.txt'

Probably my syntax is wrong. Can anyone please let me know the right syntax to Zip a Text file.

Thanks

Varsha

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

But the first  question is – Does this command-line work if you run it from a batch file?

If it doesn’t, then it’s not a SQL syntax problem, it’s a Winzip syntax problem.

...and I have a feeling –o is an extraction option code, rather than a compression option code. I’d try –a ( add) as the option code instead.

Ken McSwain Business solutions Manager

kmcswain@sydneyoperahouse.com

T+61 2 9250 7876  F+61 2 9251 7821  M 0418 659 360

SYDNEY OPERA HOUSE BENNELONG POINT

GPO BOX 4274, SYDNEY NSW 2001, AUSTRALIA

SYDNEYOPERAHOUSE.COM

Now I get error that “System cannot find the path specified.”

declare @cmd varchar(1000)

set @cmd='"\\Dell\Tessitura\Source\WinZip\winzip32.exe" -a "\\Dell\Tessitura\Source\sso_pacingdata.zip" "\\Dell\Tessitura\Source\sso_pacingdata.txt"'

select @cmd

EXEC master..xp_cmdshell @cmd

I changed T: drive to full path as I thought it may not recognise drive but still doesn’t work.

Is it permission issue for xp_cmdshell? If so what should I check?

Thanks

Varsha

Hi Varsha,

I agree with Ken, you can test it in cmd windows, and I think you should use option -a not -o too.

this will work.

declare @cmd varchar(1000)

set @cmd='"T:\Source\WinZip\winzip32.exe" -a "T:\Source\sso_pacingdata.zip" "T:\Source\sso_pacingdata.txt"'
select @cmd
EXEC master..xp_cmdshell @cmd

have fun

Ben

Please consider the environment before printing this email.

=====This message is intended for the addressee(s) named and may contain confidential information.

If you are not the intended recipient, please delete it and notify the sender.

Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Ben, I have a feeling its a WINZIP license issue. Let me get that right and test because this works correctly from command prompt (I created a batch file) but with license renewal message…

Thanks

Varsha

one more thing. you can check the event log. see what else can be found.

Please consider the environment before printing this email.

 =====This message is intended for the addressee(s) named and may contain confidential information.

 If you are not the intended recipient, please delete it and notify the sender.

 Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Ben

The total length with full path of @cmd is 129 characters so there I’m safe.

After fixing WINZIP license issue, .Bat files works perfectly on command prompt - \\Dell\Tessitura\Source\Text_Zip_Cmd.Bat

but when I run it in SQL it says “System Can not find the Path specified”

EXEC master..XP_CmdShell '\\Dell\Tessitura\Source\Text_Zip_Cmd.Bat'

In SQL forum I found out that maybe my SQL Server doesn’t have access to C: but I am not sure how to make C Drive accessible to SQL? Zip file is sitting in C:Program Files\Winzip.

Any advise on this?

Thanks

Varsha

Hi Varsha,

good day.

I think the error message maybe have something to do with "Total path and file name length must not exceed 260 characters"

http://www.question-defense.com/2010/10/25/winrar-extract-error-total-path-and-file-name-length-must-not-exceed-260-characters

the link is about winrar error message.

how about you map " Normal 0 false false false EN-AU X-NONE X-NONE MicrosoftInternetExplorer4 \\Dell\Tessitura\Source Normal 0 false false false EN-AU X-NONE X-NONE MicrosoftInternetExplorer4 \" to z: drive, create a "a.txt' file.

and test this:

declare @cmd varchar(1000)

set @cmd='"Z:\WinZip\winzip32.exe" -a "Z:\a.zip" "z\a.txt"'
select @cmd
EXEC master..xp_cmdshell @cmd

have fun

Ben

Please consider the environment before printing this email.

 =====This message is intended for the addressee(s) named and may contain confidential information.

 If you are not the intended recipient, please delete it and notify the sender.

 Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

I've create batch file:

@Echo off

"\\keenm\c$\WinZip\WZZIP.exe" "\\Dell\Tessitura\Source\sso_pacingdata.zip" "\\Dell\Tessitura\Source\sso_pacingdata.txt"

---------------

Now I run

master..xp_cmdshell 'Text_Zip_Cmd.Bat'

It runs perfectly from command prompt but in SQL i get following error.

ERROR(\\keenm\c$\WinZip\WZZIP.EXE,103): WinZip is not installed.

You must install WinZip in order to use the WinZip Command Line Support Add-On

NULL

Earlier I thought it was Winzip license issue but after fixing it and also installing command line add-on I'm getting this error.

When I run xp_cmdshell 'dir \\keenm\c$\winzip' I can see all files..

similarly when I run xp_cmdshell 'dir \\dell\tessitura\source' I can see all files..

that means I'm able to access all files, but I can't understand what’s missing when it comes to SQL Server.

Any help will be appreciated..

Thanks

Hi Varsha,

good day.

the easiest way to fix the permission issue is using  'Execute as'.

in the stored procedure, you can do something like this:

ALTER      PROCEDURE [dbo].[AP_BCP_FILE]
    (
     @file_name     varchar(255),
     @format_file     varchar(255),
     @table_name     varchar(255),
     @db_login     varchar(255),
     @db_password     varchar(255) = NULL,
     @bcp_in_out    varchar(10),
     @err_file    varchar(255) = null,
     @hint        varchar(255) = NULL,
     @cDebug    char(1) = 'N',
     @include_output char(1) = 'Y'        -- added 3/12/2007 by lwl
    )
WITH EXECUTE AS 'dbo' -----you can replace dbo to 'mynetwork\admin'
AS

you can put network admin account in sql account, then you can use that account do some special things when you need access network resource.

have fun

Ben

Please consider the environment before printing this email.

  =====This message is intended for the addressee(s) named and may contain confidential information.

  If you are not the intended recipient, please delete it and notify the sender.

  Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

WINZIP is actually installed on my computer and SQL server also the add-on, but still doesn’t work. I think I will try 7-zip. Does 7-zip have same syntax?

Hi Varsha,

Good day.

the machine runs master..xp_cmdshell 'Text_Zip_Cmd.Bat' need a copy of winzip.

So, I think you need to install winzip on the sql server if you want to schedule this as a sql job.

(I think you can use 7-zip, it is free.)

have fun

Ben

Please consider the environment before printing this email.

   =====This message is intended for the addressee(s) named and may contain confidential information.

   If you are not the intended recipient, please delete it and notify the sender.

   Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!