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
mike_macairan
mike_macairan
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 12
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.
jeetsingh.cs
jeetsingh.cs
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 618
—- 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
Hemant.R
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 240
please check .

it will work only if you are exec using sysadmin, normal user it won't work.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86612 Visits: 41098
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.
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
mike_macairan
mike_macairan
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 12
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
mike_macairan
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 12
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86612 Visits: 41098
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.
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
Hemant.R
Hemant.R
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 240
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
mike_macairan
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 12
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
mike_macairan
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 12
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.
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