Eliminating Special Characters in Sqlcmd
I have a bat file that executes a bunch of sql scripts when my application is installed. In the installation dialog, the user sets the dbname, login and password, which are then used in this bat file. Here he is:
SET _server="(local)"
SET _db=dbname
SET _user=sa
SET _pswr="!#$%^&*()<>"
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regsql.exe -S %_server% -d %_db% -U %_user% -P %_pswr% -A all
"%systemdrive%\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.exe" -S %_server% -d %_db% -U %_user% -P %_pswr% -i .\createDB.sql
pause
Everything works fine as long as the password contains no special characters. But if it does, I get the following error: "Login failed for user". "So it is obvious that something is wrong with the password. And now the question is: how can I avoid these special characters in the bat file?
PS: I tried to replace:
< - <
> - >
& - &
But that doesn't help.
Thank!
source to share
You need to escape the percent character (%) and the caret character (^) by doubling them. Here is a list of the problematic command line characters
SET _pswr="!#$%%^^&*()<>"
source to share
There are several problems with DOS batch scripting. By running the query from the command line, you can use the% character without issue. By placing the code in a batch file without calling other batch files, you double the character, i.e. %%. If you need to call another batch script inside that batch script, you can only use %%. If you need to call it in a loop, then to continue flow control you need to use the CALL statement to run the nested package. In doing so, you need to double the characters, that is, %%%%. My guess is that the scripting layer does a couple of passes over the code, replacing %%%% with %% in the first pass, and then %% with% in the second pass. I am having a problem with SQLCMD. Not sure if the problem is with the application being called or the OS (DOS) kernel. I assume this is a DOS problem.
source to share