[This was originally posted at stackoverflow.com but might be more suitable here and I'm sure it's a server configuration issue. Apologies if this is considered bad form.]
As part of our database revision control (and auto-installation) procedures we need to be able run sqlcmd.exe on various .sql files from within an ASP page. The code I'm using to do this is:
Dim cmd : cmd = "sqlcmd -S " & DATABASE_SERVER & " -U " & DATABASE_UID & " -P " & DATABASE_PWD & " -d " & DATABASE_NAME & " -i """ & scriptPath & """ -b"
Dim wshShell : Set wshShell = Server.CreateObject("WScript.Shell")
Dim return : return = wshShell.Run(cmd, 0, True)
I have the code working fine on two separate development machines. These are both running XP and required no additionally steps to get the code working. Having deployed the code to a Windows 2003 server it's having problems. The problem being that the value for return is always 1. This also happens if I try to get it to run a batch file or anything else I can think of (if I change the value for cmd to an non-existing file it bombs out as I'd expect)
I've tried adding I_USR and I_WAM to have execute permissions on both sqlcmd.exe and cmd.exe but it still returns 1. If I open a command prompt at the server and do a "runas /user:servername\i_usr sqlcmd.exe" that works fine but running from the ASP page still doesn't work.
Also, when running the .sql scripts manually everything runs smoothly so there's no problem with them.
Are there any security settings on the server that I've forgotten to change within IIS or Windows generally to make it work?
I have also tried this on another Server 2003 machine and am getting exactly the same problem.
Halp.
Have just found out what the problem was (well, my hosting provider did). Changed code by adding '%COMSPEC% /C' (where %COMSPEC% holds the path to cmd.exe) to the front of the command.
It now works just dandy. I'm not sure about the whys/hows so I'll leave the question unanswered for now for someone to answer more comprehensively if they can.