Xp_cmdshell status on SQL 2000 servers

DECLARE @xp_cmdshell_active bit

SELECT @xp_cmdshell_active = value
FROM syscurconfigs
WHERE
    comment LIKE '%command%shell%'

NOTE: It may be possible for the value to be set but not yet applied because RECONFIGURE hasn't been run.

Thanks, but no results are returning, even if I run the below. I don't any reference to command or shell.

SELECT *
FROM syscurconfigs
WHERE
    comment LIKE '%command%shell%'

Hmm, try it w/o the WHERE and look thru the results on your box:

SELECT *
FROM syscurconfigs

Yes, I've done that, but I don't see anything related to cmdshell, which is the reason why I posted this question.

Guess we need to check sysconfigures also:

SELECT *
FROM sysconfigures
WHERE
    comment LIKE '%command%shell%'

Nothing there, either.

>>How would I find the value using TSQL of whether the xp_cmdshell is turned on/off on a SQL 2000 server?

In SQL 2000, this option is always enabled.

Only from SQL 2005, this option can be enabled/disabled.

Hope this clarifies!

O, so cmdshell can never be turned off in SQL 2000?

>>so cmdshell can never be turned off in SQL 2000?
The only way to turn off is to remove/drop it. Here is how you do it:

exec sp_dropextendedproc 'xp_cmdshell'

Use it with caution, as this is an inbuilt stored procedure (Ideally, its better not to touch the inbuilt stored procs unless you are not clear about the impact)

So ideally, to find if xp_cmdshell is enabled is to see if it's installed.

Thanks