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?
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.
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!
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.