No Ben, I can run the Batch file from Windows Command prompt. I also tried scheduling Windows Task Scheduler and it successfully created the ZIP file but when I run that same batch file in SQL ;

Execute master..xp_cmdshell ‘\\dell\tessitura\source\text_zip_cmd.bat’

Then it doesn’t work.

Xp_cmdshell works fine because I did

Master..xp_cmdshell ‘dir \\dell\tessitura\source’ etc to see which files are accessible from SQL Server.

This 7zip, I’ve installed but now I’m again getting ‘The system can not find path specified’

execute master..xp_cmdshell '"\\Dell\Tessitura\Source\7-Zip\7za.exe" a "\\Dell\Tessitura\Source\sso_pacingdata.zip" "\\Dell\Tessitura\Source\sso_pacingdata.txt" -y'

I’m getting quite frustrated… But at least windows task scheduler is helping me here…

Varsha

HI Normal 0 false false false EN-AU ZH-CN X-NONE MicrosoftInternetExplorer4 Varsha,

do you mean you can run cmd in sql server machine but you fail to run it master..xpcmdshell?

7-zip has very similar syntax.

if you install 7-zip in the same location, the syntax difference will be change from "-a" to "a" ( remove the "-").

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!

Finally after so many days of R&D, i've scheduled the zipping task on Windows Task Scheduler and its solved my problem. I definitely wanted to use master..xp_cmdshell on SQL Server but looks like it will not be possible. Its privileges problem indeed. I will keep trying but for time being it’s all sorted.

Thanks a lot for helping.

regards
Varsha

HI Varsha,

Good day.

I think if you run cmd and windows task scheduler. it is running something from your local machine.

If you run something from master..xp_cmdshell, it is running from SQL server machine.

if you have sql instance install on your local machine, you login into the local instance and run master..xp_cmdshell from there.

It means if run xp_cmdshell, it starts from C:\windows\system32 on the instance of the sql server.

so the path will different.

if you install winzip on sql server, in the bat file

it should be

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

cd\

"c:\program files\winzip\winzip.exe" -a "\\dell\source\a.zip" "\\dell\source\a.txt"

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

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 can’t believe this,

As per your script, I wrote thio=s code and it actually worked…

exec master..xp_cmdshell '\\dell\tessitura\source\7-zip\7zG.exe a "\\dell\tessitura\source\sso_pacingdata.zip" "\\dell\tessitura\source\sso_pacingdataxt"'

but the SQL is not stopping. The Zip is ready but I haven’t got any return outut in SQL. It’s still executing…

what could be the problem? Does it need an output file?

Thanks a lot…

Varsha

Hi Varsha,

I found a very interesting thing in  the xp_cmshell.

this one work:

exec master..xp_cmdshell 'c:\winzip\7z.exe a "c:\a\a.zip" "c:\a\a.txt"'

this one doesn't:

exec master..xp_cmdshell '"c:\winzip\7z.exe" a "c:\a\a.zip" "c:\a\a.txt"'

---

but

c:\winzip\7z.exe a "c:\a\a.zip" "c:\a\a.txt"

and

"c:\winzip\7z.exe" a "c:\a\a.zip" "c:\a\a.txt"

both  work in cmd.

have fun

Ben

Hi Varsha,

Good day.

here is a way to use xp_cmdshell.

steps to run xp_cmdshell.

step1,


stored procedure:

create procedure lp_7zip
WITH EXECUTE AS 'mynetwork\NetWorkAmdin'

as
begin

exec master..xp_cmdshell 'c:\a\a.bat'  ---it is in sql server c: drive

end


step2


in a.bat file


--------------------------
cd\
"c:\winzip\7z.exe" a "\\mtc.com.au\mtc\Finance\Ben Gu\a\a.zip" "\\mtc.com.au\mtc\Finance\Ben Gu\a\a.txt"
------------------------------

step3


Credential account

Credential name:
##xp_cmdshell_proxy_account##

Identity:
mynetwork\NetWorkAmdin
passowrd:
**********
Comfirm passowrd:
*********

have fun

Ben

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

It worked, thanks a lot… I used –y switch to answer all Q Yes…

exec master..xp_cmdshell '\\dell\tessitura\source\7-zip\7zG.exe a "\\dell\tessitura\source\sso_pacingdata.zip" "\\dell\tessitura\source\sso_pacingdata.txt" -y'

Thanks a million…

Varsha

Ben

I can’t believe this,

As per your script, I wrote thio=s code and it actually worked…

exec master..xp_cmdshell '\\dell\tessitura\source\7-zip\7zG.exe a "\\dell\tessitura\source\sso_pacingdata.zip" "\\dell\tessitura\source\sso_pacingdataxt"'

but the SQL is not stopping. The Zip is ready but I haven’t got any return outut in SQL. It’s still executing…

what could be the problem? Does it need an output file?

Thanks a lot…

Varsha

Hi Varsha,

I found a very interesting thing in  the xp_cmshell.

this one work:

exec master..xp_cmdshell 'c:\winzip\7z.exe a "c:\a\a.zip" "c:\a\a.txt"'

this one doesn't:

exec master..xp_cmdshell '"c:\winzip\7z.exe" a "c:\a\a.zip" "c:\a\a.txt"'

---

but

c:\winzip\7z.exe a "c:\a\a.zip" "c:\a\a.txt"

and

"c:\winzip\7z.exe" a "c:\a\a.zip" "c:\a\a.txt"

both  work in cmd.

have fun

Ben

Hi Varsha,

Good day.

here is a way to use xp_cmdshell.

steps to run xp_cmdshell.

step1,


stored procedure:

create procedure lp_7zip
WITH EXECUTE AS 'mynetwork\NetWorkAmdin'

as
begin

exec master..xp_cmdshell 'c:\a\a.bat'  ---it is in sql server c: drive

end


step2


in a.bat file


--------------------------
cd\
"c:\winzip\7z.exe" a "\\mtc.com.au\mtc\Finance\Ben Gu\a\a.zip" "\\mtc.com.au\mtc\Finance\Ben Gu\a\a.txt"
------------------------------

step3


Credential account

Credential name:
##xp_cmdshell_proxy_account##

Identity:
mynetwork\NetWorkAmdin
passowrd:
**********
Comfirm passowrd:
*********

have fun

Ben

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!

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!