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