xp_cmdshell always returns NULL

[MikeMacairan]

I am having a problem for which I have done numerous online searches but have not yet found an answer. I hope someone can help with it.

SQLServer 2008r2 on Microsoft Windows 2008r2

There is a stored procedure which runs nightly. I did not write the stored procedure and I do not have the right to change it.

Here is what the stored procedure does:
* dbcc of two databases
* uses xp_cmdshell to run a zipping program to zip a directory
* puts the contents of that directory into one of the databases
* Backs the database up

This stored procedure fails about half the time on ONE server. During my troubleshooting, I discovered that it is failing on the part where it runs the zipping program.

Here is what I've noticed:

* If I open a query window and type exec xp_cmdshell 'Dir c:\' and run it, the results will sometimes be a list of everything in the C:\ directory. When I get those results and then run the stored procedure, it works.

* Other times, when I run exec xp_cmdshell 'Dir c:\' the results are NULL. When that happens, if I run the stored procedure, it fails.

What I need to know is, what can cause xp_cmdshell to sometimes work and sometimes not. It will often change from working to not working while I am still in the same query window session in Management Studio.

1) I don't think it's permissions, or it would never work. It works and then it just suddenly stops working.

2) Please don't suggest I use some other method to zip the directory because, as I mentioned, this Stored Procedure runs successfully at a lot of our sites and I am required to support it but not to change it.

I have seen this issue appear in about 10 - 15 different sites. Most people either say "it's permissions" or "Don't use xp_cmdshell for that". Neither of those help me, nor did they help the people asking the questions. Only once did I see someone find his answer and it was anti-virus, but the antivirus software has been completely removed from this system and it's still happening.

I thank you for any help you can provide.

[NeoPa]

Interesting question Mike. One I wish I could help more with, but let's see what I can do.

I'm coming more from an OS perspective on this one. I do some SQL Server but very little of the standard stored procedures available. So, what can your information tell us? If this abberrant behaviour is occurring sometimes when run from a query window when run as a user (as opposed to being run from a service by anything scheduled) then I believe that points to an issue between SQL Server and the OS. These types of issues can get confusing when a service is used, particularly when network locations (or anything related to security tokens) are involved. The root of the C: drive can have restricted security, but it's rare at that level for it to cause issues, and that tends to be an issue with scheduled tasks run under credentials other than the current user's (even sometimes when it interacts with the current user). I think it safe to say the problem won't be found looking in those areas

That hasn't really helped much looking at it, but it at least identifies a number of ares that needn't be looked at. One of these is the existing setup, as far as I can see. The differences between working and not working appear to be way outside the area of redoing the logic.

PS. Don't think too badly of people suggesting alternative approaches. It's not so great for searching through existing threads, but it can be very helpful for the OP in their time of need. Never fear, I've noted your preference on this occasion and won't go there on this one ;-)

[MikeMacairan]

Thanks for the reply, NeoPa, it's a good start.

Please understand that I was not thinking badly about people who post alternatives. I know sometimes an alternative solution is best. I was just saying that I am not allowed to change the stored procedure, so an alternative wouldn't work for ME. Also, I know this stored procedure works at dozens of other sites, so I'm really trying to find what causes it to stop working here, in order to make this one work like all the other sites.

[NeoPa]

I do understand Mike. Really. It's a perfectly valid point to make.

I've gone as far as I can for now, but I'll wait, as you do, for any further insights from anyone. Something may trigger further ideas when posted.