xp_cmdshell returns NULL

Question

Post reply

xp_cmdshell returns NULL

Luca-361445

Old Hand

Points: 354
More actions
August 25, 2006 at 3:01 am

#173789

Hi everyone.
Cannot understand why.
The same command works on every server I tested.
On the production test it does not work. (on others productions servers it works, but..)
xp_cmdshell must return only 1 or 0.
I tested and i know for sure: the external process I call handles exit code correctly. I have the same problem with a 'dir' command.
Any ideas?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Can you post the complete actual command that calls xp_cmdshell, along with how you verify the returncode?

..and also the output from the command.

/Kenneth

Luca-361445 Old Hand Points: 354 More actions · Answer 2

Sure. The code is:

/*Code starts here*/

DECLARE @retValueINT

DECLARE @strSqlVARCHAR(1000)

DECLARE @RemoteUsrVARCHAR(50)

DECLARE @RemotePwdVARCHAR(50)

DECLARE @ServerName VARCHAR(50)

SET @RemoteUsr = 'sa'

SET @RemotePwd = 'RightPasswordHere'

SET @ServerName = 'servername'

SET @strSql = 'osql -U ' + @RemoteUsr + ' -P ' + @RemotePwd + ' -S ' + @ServerName + ' -Q "select @@version"'

EXEC @retValue = master.dbo.xp_cmdshell @strSql

SELECT @retValue

/*Code ends here*/

On query analyzer resulsts is for a working server testing if another server is alive (in this case no):

output

-------------------------------------------------------

[DBNETLIB]SQL Server does not exist or access denied.

[DBNETLIB]ConnectionOpen (Connect()).

NULL

(3 row(s) affected)

-----------

1

(1 row(s) affected)

It assigns 1 to variable.

In my server it returns:

output

-------------------

NULL

(1 row(s) affected)

-----------

128

(1 row(s) affected)

And it never returns correctly 0 if the tested server is online!

It works on every other server in the factory 🙁

Lucazeo

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

This seems indeed to be a rare bird. I've never heard about it before, and Dr. Google only came up with one single thread about the same issue with MSDE. Unfortunately there was no answer in that thread.

I can't get your code to return anything else than 1's or 0's...

/Kenneth

Luca-361445 Old Hand Points: 354 More actions · Answer 4

Yes.

This is driving me crazy.

I don't think this can be a security problem. (I use sa as username running my code).

In any case...

There is a smarter way to know if another server is alive? I need this in a stored procedure.

And the sotred procedure must no stop.

If I try to access a linked serever and the server is down, sqlserver raise an error that I can't handle and breaks.

This was a fast and nice way.. but does not work anymore.

I think I'll must have an external watchdog to put in a table if a server is alive or not. But this is really a dirty way 🙁

Thanks anyway 🙂

Luca-361445 Old Hand Points: 354 More actions · Answer 5

Problem solved...

There were a lot of cmd processes blocked in the server.

So for sql was impossible to gain acces to another session of external command...

A virus was found.

With virus removed and server rebooted problem solved 🙂

dangermaus Old Hand Points: 320 More actions · Answer 6

How many cmd processes is "a lot", and where were you seeing them? I saw 2 or 3 in Task Manager, and they could have been legit ones from processes that use xp_cmdshell...

Corey