CODEWITHSUNDEEP
sql-server-2008t-sqlbatch-fileosql
Mark Bertenshaw
Mark Bertenshaw

Reputation: 5689

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')"

Upvotes: 0

Views: 1285

Answers (3)

Carl
Carl

Reputation: 11

Also, you might have issues with syntax using "LIKE '%mytext%'" in a batch running osql/sqlcmd because you need to double-up on those pesky percent signs!! But, of course it works FINE when you test it in a command prompt. I spent about an hour trying to debug that earlier today. Why didn't I immediately see and understand the issue? Unfortunately, my memory is getting more like my mom's every day... dementia runs in the family ;-(

Upvotes: 1

Mark Bertenshaw
Mark Bertenshaw

Reputation: 5689

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.

Upvotes: 1

Magoo
Magoo

Reputation: 80193

I'd change the EXIT to an EXIT /B

EXIT terminates the CMD session.

exit /b optionalerrorlevelnumber terminates the current batch, optionally returning an errorlevel.

Upvotes: 0

Related Questions