master..xp_cmdshell not working

Posted on 2004-04-28
Last Modified: 2010-08-05
I've go some procedures where I use
master..xp_cmdshell 'bcp .......
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?
Question by:xoxomos
  • 2
  • 2
LVL 34

Expert Comment

ID: 10943245
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.


Expert Comment

ID: 10943256
What is the command?

Can you run master..xp_cmdshell 'dir *.* > dir.txt'?

Author Comment

ID: 10943837
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.
LVL 34

Expert Comment

ID: 10943861
Did you check permissions?  Are you running xp_cmdshell as a sysadmin?  If you go into sqlagent properties, is the proxy account enabled?

Accepted Solution

Frostbyte_Zero earned 250 total points
ID: 10949569
These may help:

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

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

-- grant database access in master
sp_grantdbaccess 'LimitedUser'

grant exec on xp_cmdshell to LimitedUser

--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


Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now