SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


xp_cmdshell works and then doesn't work


xp_cmdshell works and then doesn't work

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87718 Visits: 41121
mike_macairan (12/6/2012)
It's not that files are being moved. The way this happens is, I open Management Studio, open a query window, type:

exec xp_cmdshell 'Dir C:\'

and execute it.

I get the results (which is a listing of everything in the root of C:\)

I can hit execute a dozen times and, at some point, the result set will change from being the contents of C:\ to NULL, but the files are exactly where they were. I can open a command prompt and do it and get the correct results every time. Once the results become NULL, I can try it a hundred more times and it won't go back to giving me the contents of C:\. If I come back, say, the next day, it may or may not start giving me the contents of C:\ again.

Once it is in that state (returning NULL for everything), the stored procedure will fail, because one of the steps uses xp_cmdshell.

I am thinking that something else is going on within SQL that blocks xp_cmdshell or that hogs it resources or something. And it's not JUST when I do a Dir; it's with anything I try to do using xp_cmdshell.

I know this is a difficult question because I did extensive online searches before posting my question. A lot of people have had this problem, most of the answers are off-track because it's kind of hard to explain the symptoms--and the only one of those posts where the solution was found was one where the Antivirus program was preventing xp_cmdshell. I don't have an antivirus program on here (I took it off for troubleshooting) and the firewall is turned off.


Thanks for the feedback on this. I'm not sure what the problem is because I've just tried what you did on two of my servers and my local box. I even tried the following...

exec xp_cmdshell 'Dir C:\' 
GO 99




No sign of the NULL problem you're reporting. This is also the first time I've ever heard of someone having such a problem but, of course, I've never searched for it before, either.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JustOffal
JustOffal
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 161
Did you ever find out why xp_cmdshell only worked intermittently? I have a similar problem, but mine is running BCP and it just hangs. I have to restart the SQL Server to get BCP to stop.

The BCP code used to work, but then it just stopped working a few days ago and hasn't worked since. It is not BCP, because I can run that manually from the Cmd prompt with no problem. I cannot manually run xp_cmdshell with the BCP command from SSMS and I get a syntax error.

Any insights would be appreciated. Thanks!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87718 Visits: 41121
Sorry... responded to an older post on this thread that had already been answered so removed my duplication.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87718 Visits: 41121
JustOffal (5/12/2014)
Did you ever find out why xp_cmdshell only worked intermittently? I have a similar problem, but mine is running BCP and it just hangs. I have to restart the SQL Server to get BCP to stop.

The BCP code used to work, but then it just stopped working a few days ago and hasn't worked since. It is not BCP, because I can run that manually from the Cmd prompt with no problem. I cannot manually run xp_cmdshell with the BCP command from SSMS and I get a syntax error.

Any insights would be appreciated. Thanks!


Please post the command that's giving the error and the actual error captured from the screen. Thanks.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16806 Visits: 10084
JustOffal - I'm incredibly curious about this one, too. Please post the command and the output.

Jeff - You know I'm thinking of your presentation that isn't ready yet. ;-)


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87718 Visits: 41121
Ed Wagner (5/13/2014)
JustOffal - I'm incredibly curious about this one, too. Please post the command and the output.

Jeff - You know I'm thinking of your presentation that isn't ready yet. ;-)


That's what I'm thinking, as well.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JustOffal
JustOffal
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 161
Hi, I'm sorry but I cannot reproduce the error anymore. I removed a few comments from the sp that calls bcp, and it started working again. The issue no longer exists and I don't want it to come back.

Thanks for your responses!
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16806 Visits: 10084
Glad to hear it worked out for you in the end.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
abair34
abair34
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 315
I'm not sure if this will help but the issue you're describing reminds me of what happens when you exhaust the non interactive desktop heap in windows. Once that happens any attempts to launch more cmd.exe's or bcp.exe's will fail. I'm guessing when this occurs you'll be able to go to the task manager and if you look for processes started by your SQL Server's service account you will not see the typical conhost.exe running. The only other time I remember seeing something similar is when you launched a process from xp_cmdshell and that process never terminated. So in that case you'll see conhost.exe, cmd.exe and some process you told it to launch (like redgate's command line executable). Sometimes then you can kill that launched executable and cmd will complete and then you'll restore connectivity to your xp_cmdshell session. As for the non interactive desktop heap you typically have to make some registry changes to fix it. Another symptom would be if you launched a new process you'd probably find high numerical values for your PIDs (because thousands of other processes were launched before it). Beware that the same high value could just be due to your server being up for a long period of time.

Here's a doc on how to bump up the heap size on windows servers to allow you to launch more commands. You could give this a shot to see if it alleviates the issue.
https://support.microsoft.com/en-us/help/947246/you-may-receive-an-out-of-memory-error-message-because-of-the-desktop-heap-limitation

Hope this helps
-Mike
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search