sde commands using the xp_cmdshell utility in sql server 2008

Question

OS: Windows Server 2008
License: EDN Developer 
SDE Version: 9.3
SQL Server Version: 2008 SP 1

I am interested in getting a test environment set up for our GIS database. I want to be able to test new SQL queries, jobs, code, etc. on the GIS data without having to kick everyone off of the system to do it (in production). I have been able to set things up in the Database identically to our production database. The problem is issuing sde commands from the xp_cmdshell utility in SQL Server. I can execute commands successfully from the production server, however when trying the same on the development server I get the following error:USE GIS
GO

DECLARE @SDECom varchar(50)
SET @SDECom = 'sdemon -o info -I users'

EXEC xp_cmdshell @SDECom

'sdemon' is not recognized as an internal or external command, operable program or batch file.

I've ruled out the SDEHOME environment variable path because I can execute commands successfully from the DOS command line. I have also ruled out permissions with xp_cmdshell itself because I can execute xp_cmdshell commands not related to sde wouthout any problem. It is the combination of sde commands using the xp_cmdshell utility in sql server. What could possibly be wrong?

VinceAngelo · Answer

Windows passes environment variables when a process is started.  The parent process for
xp_cmdshell is the SQL-Server instance, which didn't have the SDEHOME and PATH set
at the time it started, hence your problem.

Yes, restarting the service should have accomplished the same as a reboot.

-  V

JohnFell · Answer

Thanks, that information really helped.

I issued the "Set" command using the extended stored procedure xp_cmdshell and it returned environment variables for the machine. The SDEHOME environment variable was not in the returned result. I had recently installed SDE but the server had not been rebooted since them.

Why did the SDEHOME variable work from the DOS shell but not from the xp_cmdshell? Do I need to restart the SQL Server services for it to pick up the environment variable change using xp_cmdshell?

After rebooting the server and issuing the "Set" command from the xp_cmdshell the SDEHOME environment variable was listed among the results and I was able to issue the sde commands that I had wanted. Thanks for your help again!

VinceAngelo · Answer

Have you tried executing a "Set" command, to see what the PATH contains?  It's possible
that the process is stripped of non-essential environment for security reasosns.

Have you tried running a batch script from 'xp_cmdshell' with the ArcSDE command inside
the .BAT file? Might as well tuck a "Set" in there too.

- V