How can I run sqlcmd.exe from an ASP page? (Free hat)

Question

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 on my development machine (running XP) but now that I've deployed it to our 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?

Thanks in advance the internet.

Answer 1

There is only a very very small chance that you actually want this!

To execute SQL Commands from ASP see this article: http://www.w3schools.com/ADO/ado_examples.asp

Or this one:

http://www.domaindlx.com/asp_db.asp

If your SQL Scripts are too large for such thing you also don't want them running in the context of an ASP page. In that case you'll need to kick off a background worker which executes the sql script for you.

Answer 2

You should change

cmd = "sqlcmd -S ...

to

cmd = "\windows\sqlcmd.exe -S ..

(Or whatever the full path to sqlcmd.exe is without spaces)

Just to rule out path problems.

Answer 3

The problem was solved by changing the first line to:

Dim cmd : cmd = "%COMSPEC% /C sqlcmd -S " & DATABASE_SERVER & " -U " & DATABASE_UID & " -P " & DATABASE_PWD & " -d " & DATABASE_NAME & " -i """ & scriptPath & """ -b"