xp_cmdshell works and then doesn't work

Question

Post reply

xp_cmdshell works and then doesn't work

mike_macairan

Mr or Mrs. 500

Points: 563
More actions
December 4, 2012 at 11:18 am

#285769

A stored procedure intermittently fails, and it appears to be because xp_cmdshell stops working.
If I try it manually, here is what I get.
* Open Management Studio
* exec xp_cmdshell 'Dir c:\' (I get valid results in the form of a list of the directory contents for c:\)
* Do that a few more times and get the same results.
* Eventually, I will try it and rather than getting the contents of the directory, I get NULL and will keep getting NULL no matter how many times I try
* I have never SEEN it start working again, but I have come back a day or two later and it was working
Please note that this happens with ANYTHING I try to do using xp_cmdshell. Using 'Dir c:\' is just an example.
I can try anything with xp_cmdshell, have it work for a while, and then I will see it just stop working.
I don't get an error saying xp_cmdshell isn't configured, and I don't think it's permissions or it wouldn't just start working again
When exec xp_cmdshell is working, so does the stored procedure.
When exec xp_cmdshell is not working, neither does the stored procedure
This is because the stored procedure calls xp_cmdshell when it's running
I am just really frustrated trying to figure out what could cause xp_cmdshell to just suddenly stop working and to then start working again later.

Viewing 15 posts - 1 through 15 (of 20 total)

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

jeetsingh.cs SSCommitted Points: 1761 More actions · Answer 1

—- To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

GO

—- To update the currently configured value for this feature.

RECONFIGURE

GO

Check if this works for you.

Hemant.R Ten Centuries Points: 1286 More actions · Answer 2

please check .

it will work only if you are exec using sysadmin, normal user it won't work.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Also check other people's jobs and stored procs which may be intentionally turning off xp_CmdShell. People do this thinking they make its use more secure. That's false, of course, but they do it anyway.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mike_macairan Mr or Mrs. 500 Points: 563 More actions · Answer 4

Thanks for the suggestion. I have tried enabling xp_cmdshell like this before and it didn't make any difference. If you try to run xp_cmdshell and it is not enabled, I believe you get an error message telling you so. I never get that error. I get results, then results, then results, then NULL and once I get NULL, I continue getting it for a long time. If I come back later, it may work again, but will eventually go back to returning NULL.

mike_macairan Mr or Mrs. 500 Points: 563 More actions · Answer 5

Checking other people's jobs is a good suggestion, too, but this server was built for a specific purpose and should contain only the jobs that are part of the standard build--which would match all the other servers that were put out by this company, and which are working just fine.

I am in agreement that there may be something else happening, though, that is blocking xp_cmdshell--simply based on the way that this happens intermittenly. If it was a consistant failure, maybe it's a permissions or configuration problem. If it works and then stops and then works again, there must be something else going on that blocks xp_cmdshell. I just can't find whatever it is.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

Could it be that the files are moved out of the directory, at some point, and that the DIR is reporting correctly?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hemant.R Ten Centuries Points: 1286 More actions · Answer 7

hi,

you can also find reason in sql server job log.

for that ,you have to give output file in your job .

it will written log and you will exact reason over there.

mike_macairan Mr or Mrs. 500 Points: 563 More actions · Answer 8

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.

mike_macairan Mr or Mrs. 500 Points: 563 More actions · Answer 9

I have looked in the output log for the job. It just says that zipping up the directory failed, but doesn't give a reason for the failure. Zipping the directory is what's being done through xp_cmdshell; the job (and stored procedure) work fine at many other sites. I am sure that, once I find out what makes xp_cmdshell suddenly start returning NULL, the job and stored procedure will have no more problems.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JustOffal SSC Eights! Points: 911 More actions · Answer 11

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 SSC Guru Points: 1004414 More actions · Answer 12

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 13

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 14

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