0

I have written a batch script to get a particular value or update values from sql database. It works only for some times,listed below.

case 1:

  • First time not works (not update) but returns - 1 rows affected

  • Second time it works (update well in db) returns - 1 rows affected

case 2:

some times thrown an error (Sqlcmd: 'username=': Invalid argument. Enter '-?' for help.)

Find my cmd error here.

Please find my script and sql code below.

main.bat

@ECHO OFF

:: DB Properties
SET dbPath=XXXX 
SET dbUsername=YYYY
SET dbPassword=ZZZZ
SET dataBaseName=KKKK

:: SQL Properties
SET originId=50

ECHO DB NAME -  %dataBaseName%

ECHO Select an option
ECHO 1.Token
ECHO 2.Activate

SET /P option=Enter an option:

IF "%option%"=="1" ( SET /P inputUserName=Enter username: 
    sqlcmd -S %dbPath% -U %dbUsername% -P %dbPassword% -i token.sql -v dbName=%dataBaseName% username=%inputUserName% )

IF "%option%"=="2" ( SET /P inputUserName=Enter username: 
    sqlcmd -S %dbPath% -U %dbUsername% -P %dbPassword% -i update.sql -v dbName=%dataBaseName% username=%inputUserName% orgId=%originId% )

ECHO Executed Successfully ..!!

::PAUSE

token.sql

use $(dbName);

select VerificationCode from users where username = '$(username)'

update.sql

use $(dbName);

UPDATE users
SET passwordHash = (SELECT PasswordHash FROM users WHERE userid = $(orgId)),
    passwordsalt = (SELECT passwordsalt FROM users WHERE userid = $(orgId)),
    isActive = 1,
    emailConfirmed = 1
WHERE username = '$(username)'

How to resolve this problem ?

Aravinthan K
  • 1,763
  • 2
  • 19
  • 22
  • 2
    "Does not work the first time, but does the second time" usually is a hint to a [delayed expansion problem](http://stackoverflow.com/a/30284028/2152082) (there are two `if` blocks where this problem occures) – Stephan Jul 26 '16 at 06:32
  • Thank you @Stephan. It works and got a nice explanation :-) – Aravinthan K Jul 26 '16 at 07:30

1 Answers1

0

Reference here

1. Enable the delayed expansion

setlocal enabledelayedexpansion

2. Change the passed reference from %inputUserName% to !inputUserName!

It works !!

Community
  • 1
  • 1
Aravinthan K
  • 1,763
  • 2
  • 19
  • 22
  • Although this is the correct answer to your question, why don't you `set /p inputUserName=Enter username: ` once after `SET /P option=Enter an option:` and then just `if "%option%"=="1" sqlcmd ...` and `if "%option%"=="2" sqlcmd ...` ? – Stephan Jul 26 '16 at 13:09