VB.NET - Call isql with args from VB.net - Asked By Howard Taylor on 29-Mar-13 01:38 PM

We are trying to move away from clear text passwords used by .bat files.  Our .bat files call isql and pass a SQL Server stored procedure to run.  The SP sends an email.  Inside the SP are print statements that then get directed to a text file used as a log of the email just sent.

Rather than do this in the .bat file with clear text with a call such as:

isql -Smyserver -Uuser -Ppassword -n -Q"EXIT (EXEC usp_mail_send arg1,arg2)" > logEmailDT.txt

 I have encrypted the password file and written a console app to read the encrypted file parsing the log-on data then I call isql from inside the console.

Please look at this: (Note EnvObj contains all of our log-on data unencrypted)

Dim DoIt As String = "isql.exe -S" + EnvObj.DBServer + " -U" + EnvObj.DBAUserId + " -P" + EnvObj.DBAPassword + " -n -Q""EXIT (EXEC usp_mail_send " + arg1 + "," + arg2 + ")"" > logEmailDT.txt"

Shell(DoIt)

This will not error but it doesn't send the email or create our log file.  If I stop the code on the Shell() cmd and get the contents of DoIt as a string then open a cmd window and paste it in and hit ENTER it works.  That tells me the string is properly formatted.

Why won't it work when I try to shell it out?

It is interesting to note that if I remove the spaces around the chevron (>) that redirects output to the log file it will send the email but does not create the log file:

Dim DoIt As String = "isql.exe -S" + EnvObj.DBServer + " -U" + EnvObj.DBAUserId + " -P" + EnvObj.DBAPassword + " -n -Q""EXIT (EXEC usp_mail_send " + arg1 + "," + arg2 + ")"">logEmailDT.txt"


Thanks