Dear Group:
This is my first attempt at using the xp_cmdshell command, but right now, I am trying to run it inside SSMS. We need to create a procedure to unzip files so that we can import the data into our tables. I am trying to use PKWare SecureZip to do this, as I have it installed on my local machine.
Here is the command I am attempting to run
EXEC master..xp_cmdshell 'C:\"Program Files"\PKWARE\PKZIPW\pkzipw.exe -extract=all S:\TestUnzipFolder\dataLog.txt.gz'
However, I am getting an error about : "The system cannot find the path specified.". I know the paths are valid, but is this command trying to execute SecureZip on the server and the error is saying the application is not installed on the SQL Server?
Or is there some other mechanism to extract zipped files that I could use other than this?
January 14, 2021 at 3:26 pm
... but is this command trying to execute SecureZip on the server and the error is saying the application is not installed on the SQL Server?
Precisely this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
When you run such code, everything is pretty much "server-centric" when it comes to drive letters. For example, you're referencing the "C:" drive in your code... when that executes on the server, it looks at its own "C:" drive. Of course, it can also use UNCs to point at shares and the login for either the SQL Server or SQL Server Agent needs to have the right privs on that share.
For xp_CmdShell, you also need to make sure that individual users (other than the true DBAs that typically have sysadmin privs) are NEVER granted privs to execute xp_CmdShell. The MUST be relegated to only using stored procedures that typically has an "Execute as Owner" clause in it and the owner is (usually) the disabled "SA" login or some disabled service login that has sysadmin privs.
Also, when you're testing from your desktop, you may run into the Kerberos "double-hop" security feature that may stop you if you are trying to execute xp_CmdShell directly yourself. The work around for properly authorized development, of course, is to create a proper stored procedure for the given task and execute that.
Although it's not difficult to do, you MUST also learn how to find and stop an xp_CmdShell run and not just the CMD that it spawns. You need to stop the entire process tree or you could leave a hung run running. Of course, since it would never "return" through xp_CmdShell, that can also leave a stuck spid. Killing the entire tree of the process is going to be needed to overcome that. And, no, you don't need to "restart the SQL Server Service" to do this. You can use Task Manager on the server itself to do it all.
There's another way to find and kill a bad xp_CmdShell run with a stored procedure (that ironically uses a different call to xp_CmdShell) but I've not taken the time to modify my version of it for Windows 10. It's buried on my "To Do" list somewhere. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2021 at 4:46 pm
You asked if there are other ways to accomplish your goal - and yes, there are several...all of which start outside SQL Server. Two (of the many) methods are:
Using these - you can unzip the files and load them into the database in the same process. I prefer SSIS for these kinds of processes - other will say Powershell is better, and yet others will recommend doing everything in SQL Server using xp_cmdshell and BCP/BULK INSERT.
The best one - in my opinion - is the one that is easiest for you and your team to implement, manage, maintain and support. It does not good to setup and use xp_cmdshell and BCP if you are the only one who can support it because the rest of your team all use Powershell (or SSIS).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 14, 2021 at 5:29 pm
regarding using "PKWare SecureZip" - this is a commercial software - I suggest you also look at 7zip - quite good and free.
all remaining comments above apply.
January 14, 2021 at 7:48 pm
Phil hit the nail on the head as to the cause. The cmd runs on the server, not on your PC. So, you must keep that in mind when using it and write accordingly.
Please pay attention to what Jeff said about privs for using it. People who aren't DBAs shouldn't be running it directly.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply