Search code examples
sqlbatch-filescriptingsqlcmd

Why does the following sqlcmd call returns 0 rows when in a bat file? (passing manually)

I am having trouble writing up a batch script. I've narrowed it down to what seems to break and I am providing an example. The following code, when pasted into a console returns 10 rows:

set TESTRUNID=111222
set QUERY="select distinct col1 from Table where col2='%TESTRUNID%' and col3 LIKE '%es'"
start /B /wait sqlcmd -S dahost -U usr -P pwd -Q %QUERY% -o resfile.txt

When I put it in a batch script, it returns 0 rows!

@echo off
setlocal EnableDelayedExpansion
REM remark
REM remark
set TESTRUNID=111222
set QUERY="select distinct col1 from Table where col2='%TESTRUNID%' and col3 LIKE '%es'"
start /B /wait sqlcmd -S dahost -U usr -P pwd -Q %QUERY% -o resfile.txt
Solution

  • I think you are mixing up the use of the percent sign to mean (1) batch variable expansion, and (2) SQL wildcard. Inside a batch file, use a double %% sign for an SQL wildcard:

    set QUERY="select distinct col1 from Table where col2='%TESTRUNID%' and col3 LIKE '%%es'"

    The double % sign gets translated to a single % sign before it's passed to SQLCMD.