0

While trying to create a batch file that runs a SQL script by calling out to sqlcmd.exe, I'm trying to get the batch file to detect for the presence of the exe and add it to %PATH% automatically before executing. I can guarantee that it will be somewhere on the host but not where.

Looking at the many examples on SO, this suggests that the most robust way of detecting if string A is in string B is to echo out and pipe to findstr to account for case insensitivity, etc.

With this in mind, I have the following in my batch file;

@echo off

REM Ensure we can find the sql interpreter
echo Checking for SqlCmd.exe, please wait...
setlocal EnableDelayedExpansion
for /f "delims=" %%F in ('dir "%programfiles%\sqlcmd.exe" /s/b') do (
    set filepath=%%~dpF
    echo "Found SQL interpreter in: !filepath!"

    REM is it part of the PATH already?
    echo %path% | findstr /c:"!filepath!" 1>nul
    if errorlevel 1 (
        echo "SqlCmd is not part of the current ENVAR PATH, setting..."
        set path=%path%;!filepath!
    )
)

And has the following output;

Checking for SqlCmd.exe, please wait...
\Microsoft was unexpected at this time.

Despite the text Found SQL interpreter in: not appearing in the output, the actual issue seems to be in the next executable line (tested by removing it and running):

echo %path% | findstr /c:"!filepath!" 1>nul

It seems to be echoing out the whole line and executing rather than echoing out the %path% and piping to findstr. I've tried adding quotes to no effect.

I'm almost there but missing an encoding step/tweak to get this to work.

Update 1 The PATH variable contents on my test machine is;

C:\Program Files\Common Files\Oracle\Java\javapath;C:\Program Files\Microsoft MPI\Bin\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\OpenSSH\;C:\Program Files (x86)\Microsoft SQL Server\150\Tools\Binn\;C:\Program Files\Microsoft SQL Server\150\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\;C:\Program Files\Microsoft SQL Server\150\DTS\Binn\;C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\;C:\Program Files\Microsoft SQL Server\130\Tools\Binn\;C:\Program Files\dotnet\;C:\ProgramData\chocolatey\bin;C:\Program Files (x86)\Windows Kits\10\Windows Performance Toolkit\;C:\Program Files\Git\cmd;C:\Users\User\.dotnet\tools;C:\Program Files\Java\jdk-17.0.3.1\bin;C:\Users\User\AppData\Local\Programs\Microsoft VS Code\bin;C:\Program Files\Azure Data Studio\bin
DiskJunky
  • 4,750
  • 3
  • 37
  • 66
  • 1
    Would be useful if you posted the actual name(s) of the `Microsoft`-containing directorynames from your `path` so we can apply practice in place of theory. Some problems with your code: `filepath` will have a terminal backslash. `Path` elements will usually be of the form `d:\dirname;` or `d:\dirname\;` & the last element may not be terminated by `;`. `Echo`ing `%path%;` would seem a good idea, as would /c:"!filepath!;" /c:"!filepath:~0,-1!;" in the 'findstr`. – Magoo Apr 19 '23 at 17:10
  • 1
    ...and on general principles, use `set "var=value"` for setting string values - this avoids problems caused by trailing spaces. Don't assign `"` or a terminal backslash or Space. Build pathnames from the elements - counterintuitively, it is likely to make the process easier. – Magoo Apr 19 '23 at 17:10
  • 1
    There should be deleted `setlocal EnableDelayedExpansion` or modified to `setlocal EnableExtensions DisableDelayedExpansion` as delayed variable expansion is not really needed here on removing next `set filepath=%%~dpF` and use everywhere instead of `!filepath!` `%%~dpF`. There is a space missing in the `dir` command line between `/s` and `/b`. `cmd.exe` detects that syntax error after several file system accesses and automatically corrects the error, but better is using the correct syntax with a space between each argument passed to command __DIR__. – Mofi Apr 19 '23 at 17:27
  • 2
    Use `echo "%path%" | %SystemRoot%\System32\findstr.exe /I /C:"%%~dpF" 1>nul` because of `%path%` expands to a string containing `)` which is interpreted outside of a double quoted argument string as __FOR__ command block closing and not `)` at end of the posted batch file which is the cause for the error message. – Mofi Apr 19 '23 at 17:30
  • 1
    I recommend to read [How does the Windows Command Interpreter (CMD.EXE) parse scripts?](https://stackoverflow.com/questions/4094699/) and [debugging a batch file](https://stackoverflow.com/a/42448601/3074564) to better understand how the posted command lines are processed and such syntax issues. The usage of `echo "%path%"` is in real no solution on `PATH` contains a folder path enclosed in `"` because of containing a semicolon. That is fortunately very rare. Better would be `path | %SystemRoot%\System32\findstr.exe /I /L /C:"%%~dpF" 1>nul` as `path` is also a command and not only a variable. – Mofi Apr 19 '23 at 19:01
  • 1
    That solves the syntax problem, but it does not make the code really working. Another problem is that `%%~dpF` expands always to a path ending with a backslash while the folder paths in value of environment variable `PATH` can end with a backslash, but can be also without a backslash at end which is more common. A possible solution would be `for %%J in ("%%~dpF.") do path | %SystemRoot%\System32\findstr.exe /I /L /C:"%%~fJ"`. See also [Issue 7: Usage of letters ADFNPSTXZadfnpstxz as loop variable](https://stackoverflow.com/a/60686543/3074564) why `F` can but should not be used as loop variable – Mofi Apr 19 '23 at 19:10
  • 1
    However, this command line is still not 100% safe in usage as it can result in false positives because of not making sure that an __entire__ folder path in value of `PATH` matches the searched string. It is also possible that a longer folder path in `PATH` value matches the searched folder path. Yes, checking if a folder path is already in `PATH` value is a nightmare on using a batch file. I suggest reading rojo´s answer on [How to set PATH environment variable in batch file only once on Windows?](https://stackoverflow.com/a/31889785/3074564) – Mofi Apr 19 '23 at 19:14
  • @Mofi, thank you for all the comments and suggestions here, there's a lot of really good info! As you said, getting it *right* when setting the `%PATH%` is a nightmare, and one I think I should avoid if I can. The answer posted below gets me what I need but it is a shame there isn't a cleaner way to set things up with `%PATH%`. – DiskJunky Apr 19 '23 at 19:32
  • I recommend to read also [How to check if directory exists in %PATH%?](https://stackoverflow.com/a/8046515/1012053) written by [Dave Benham](https://stackoverflow.com/users/1012053/dbenham). – Mofi Apr 19 '23 at 19:48

1 Answers1

0

After very helpful suggestions from Magoo and Mofi and some experimentation, I tweaked the logic such that it was

  • Correctly accounting for case (I had ommitted the /I)
  • Fixed the sytax issue of /s/b to /s /b
  • Added the new setlocal EnableExtensions flag
  • Changing the loop variable to J

I had also applied the other suggestions but they had no defacto effect (valid subtlties and distinctions aside) on the running of the batch file. I then hit the hurdle of;

set path="%path;%%~dpF"

This resulted in %PATH% having a leading and trailing " which broke all references to anything %PATH% was referencing. Leaving out the " meant that the SET resulted in the original error.

Instead I ditched my attempt to set %PATH% entirely and just used the found reference to SqlCmd.exe directly. The final batch file is like so;

@echo off

REM Ensure we can find the sql interpreter
echo Checking for SqlCmd.exe, please wait...
setlocal EnableExtensions
setlocal EnableDelayedExpansion
for /f "delims=" %%J in ('dir "%programfiles%\sqlcmd.exe" /s /b') do (
    echo SqlCmd found at %%~dpJ
    set sqlcmd="%%J"
    goto exit
)

:exit
%sqlcmd% /?

For those looking to still set the %PATH% variable, and read up on variable expansion, etc, Mofi's links are listed below for convenience;

DiskJunky
  • 4,750
  • 3
  • 37
  • 66
  • 1
    I suggest reading [this answer](https://stackoverflow.com/a/67039507/3074564) for details about the commands __SETLOCAL__ and __ENDLOCAL__ and do __not__ run this command twice first for enabling command extensions and second for enabling delayed variable expansion. Enabled delayed expansion is not necessary for the code in posted answer but it results in folder paths containing by chance one or more `!` to be not correct processed by the commands inside the __FOR__ loop. Exclamation marks in the fully qualified file name are interpreted as beginning/end of delayed expanded variable reference. – Mofi Apr 21 '23 at 04:58