master..xp_cmdshell not working

I've go some procedures where I use
master..xp_cmdshell 'bcp .......
or
master..xp_cmdshell 'copy.....'
These are working fine on two installs for SQLServer 2000 running on Windows2000 server.  Now there's a new Windows 2000 install on a different box that I need to use the xp_cmdshell on , but it just returns 'NULL' and does nothing.
Any ideas why?
xoxomosAsked:
Who is Participating?
 
Frostbyte_ZeroConnect With a Mentor Commented:
These may help:

-- retrieve the proxy account to check that it's correct.
EXEC master..xp_sqlagent_proxy_account N'GET'
go

-- set the proxy account
xp_sqlagent_proxy_account N'SET'
                        , N'<mydomain>'
                        , N'<ntuser>'
                        , N'<ntuser password>'
go

-- grant database access in master
sp_grantdbaccess 'LimitedUser'
go

grant exec on xp_cmdshell to LimitedUser
go

--Corresponds to the Enterprise Manager SQL Agent property page
-- Job System tab.  Sets the value of "Only users with SysAdmin
-- privileges can execute CmdExec and ActiveScripting job steps"
--  1 Turns on the restriction
--  0 Turns off the restriction and allows non sysadmin logins
--               to do this and to run xp_cmdshell
EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0
go


0
 
arbertCommented:
Either the new installation isn't setup to allow non-sysadmins the ability to run it (you should get a permissions error though) and the proxy account for SQLAgent needs to be enabled, or the command isn't right for the new server.

If you actually open a DOS prompt on the new server and execute your command that you pass to xp_cmdshell, what do you get?  Maybe there is a path error or something that xp_cmdshell isn't displaying back.

Brett
0
 
Frostbyte_ZeroCommented:
What is the command?

Can you run master..xp_cmdshell 'dir *.* > dir.txt'?
0
 
xoxomosAuthor Commented:
master..xp_cmdshell 'dir *.* > dir.txt
just returns a 'NULL'.
Commands will run fine from dos shell
i.e dir *.* will give a listing of files in the directory.
0
 
arbertCommented:
Did you check permissions?  Are you running xp_cmdshell as a sysadmin?  If you go into sqlagent properties, is the proxy account enabled?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.