TSQL statement in OSQL fails when called from a batch file called from a batch file

I am automating various EXEs via a batch file, including calling an existing batch file which in turn executes various TSQL statements via OSQL.exe.

The existing batch file works fine. However, when called by my batch file, a call to osql.exe fails, which leads to the batch file quitting itself.

:check_user_privs

%OSQLPATH% %CONNECTSTRING% -S "%SERVER_INSTANCE%" -d "%DBNAME%" -Q "DECLARE @userName varchar(100) ; set @userName = user_name() ; IF IS_SRVROLEMEMBER ('sysadmin') != 1 RAISERROR ('This user ''%s'' is not a member of the ''sysadmin'' group.' , 16 , 127 , @userName )"  > /nul
if errorlevel 1 (
    echo ** ERROR - The user is NOT a sysadmin member on "%SERVER_INSTANCE%" - Exiting ** 1
    echo.
    pause
    exit
)

Suffice to say, the error occurs. With ECHO ON, that first line becomes:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE" -U"ADMIN" -P"PASSWORD" -S "SQL_SRVR10" -d "DB01" -Q "DECLARE @userName varchar(100) ; set @userName = user_name() ; IF IS_SRVROLEMEMBER ('sysadmin') != 1 RAISERROR ('This user ''ADMIN'' is not a member of the ''sysadmin'' group.' , 16 , 127 , @userName )"  > /nul

This line is identical when I am calling the script directly from the command line or from my own batch file.

I've manually captured the environment variables that are set when this batch file is called from my batch file. I then set them manually at the command line and then run the batch file manually. No repro.

I changed the TSQL statement to simply output the return value of the function IS_SRVROLEMEMBER(). Both from calling from my script and command line, the output is identical - "1":

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE" -U"ADMIN" -P"PASSWORD" -S "SQL_SRVR10" -d "DB01" -Q "SELECT IS_SRVROLEMEMBER ('sysadmin')"

Solution

Well, I spent some more time on this, and I noticed that the error didn't occur if I used CMD.EXE /C or START /B /WAIT to call the other script. So it was definitely something about the state of the CMD.EXE process which was causing the problem.

In addition, I decided to ECHO each line to a text file. And I noticed that there was a subtle difference:

ECHO "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE" -U"ADMIN" -P"PASSWORD" -S "SQL_SRVR10" -d "DB01" -Q "DECLARE @userName varchar(100) ; set @userName = user_name() ; IF IS_SRVROLEMEMBER ('sysadmin') != 1 RAISERROR ('This user ''ADMIN'' is not a member of the ''sysadmin'' group.' , 16 , 127 , @userName )"  > out.txt

When you look at out.txt, it contains:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE" -U"ADMIN" -P"PASSWORD" -S "SQL_SRVR10" -d "DB01" -Q "DECLARE @userName varchar(100) ; set @userName = user_name() ; IF IS_SRVROLEMEMBER ('sysadmin') = 1 RAISERROR ('This user ''ADMIN'' is not a member of the ''sysadmin'' group.' , 16 , 127 , @userName )"

The difference is that the exclamation character (!) has disappeared. This explains why the comparison always evaluates to false, because the comparison is the complete opposite of the one you expect.

I eventually figured out that what is happening is that the CMD.EXE which calls into this script is currently in delayed environment variable expansion mode, due to a previous execution of SETLOCAL ENABLEDELAYEDEXPANSION. When CMD.EXE processes the line, the ! character marks the beginning of an expansion, so it automatically strips it out. However, the next character is an equals character (=), which is not allowed in environment variables, so the processing immediately cancels the variable expansion, and instead processes that character. So only the = character gets set as an argument to OSQL.EXE.

The proper solution is to immediately before CALLing the script, execute SETLOCAL DISABLEDELAYEDEXPANSION, and afterwards execute ENDLOCAL.