Unzipping files using CMDShell Getting Access denied

Question

Unzipping files using CMDShell Getting Access denied

Jeffery Williams

SSCertifiable

Points: 6441
More actions
May 21, 2010 at 7:29 am

#94907

I am running the following script to unzip some files and getting a returned result (Access is denied).
SQl 2008. From what I can tell I should have permission. Also there have been other times I use CMDShell with the same account and it works. For example The files I want to unzip; I then import these files us cmd_Shell.
Can someone help please.
**
declare @ZipFileName VarChar(50)
declare @SQLCommand VarChar(400)
SET @ZipFileName = '*.Zip'
SET @SQLCommand =
'exec master..xp_cmdshell ' + '''' + 'C:\Users\jwilliams\Documents\VCLZip -e C:\Users\jwilliams\Documents\EConnect\FirstMerrit\SourceFiles\'
+ @ZipFileName + ' C:\Users\jwilliams\Documents\EConnect\FirstMerrit\SourceFiles\unzip\' + ''''
EXEC (@SQLCommand)
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

your right, I think it's permissions;

the account that SQL is running under, are you SURE it has access to the C:\Users\jwilliams\Documents\ folder and all it's sub folders?

since you've used CmdShell and zip before, it might be that the account SQL is running under has access to C:\Data or something, but not your own private folders.

try moving the files to somewhere not under your personal folders and see if it works...

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!

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 2

Good idea, I will try that and update.

Thanks.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 3

Now I have the worst situation I think I could have with this process. I downloaded a freeware unzip utility and with command line the attched works just fine. FROM within SQL I run the attached and get no errors. Output just says NULL. MESSAGES: 1 Row(s) Affected

HOWEVER, there is no effect on the zip file, it is not being unzipped?? Very strange. Any ideas anyone? Any way I can see what is going on? Really hard to find a solution when SQL is not even reporting an issue

Again when I type in ( C:\cam\camunzip\cuz -q -p:P81059999 C:\Users\jwilliams\Documents\EConnect\FirstMerrit\SourceFiles\') in the Command prompt the file unzips.

declare @ZipFileName VarChar(50)

declare @SQLCommand VarChar(400)

SET @ZipFileName = '810571sn.zip'

SET @SQLCommand =

'exec master..xp_cmdshell ' + '''' + 'C:\cam\camunzip\cuz -q -p:P81059999 C:\Users\jwilliams\Documents\EConnect\FirstMerrit\SourceFiles\'

+ @ZipFileName + 'C:\Users\jwilliams\Documents\EConnect\FirstMerrit\SourceFiles\unzip\' + ''''

EXEC (@SQLCommand)

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh