This works:
SET server=MyServer
SET db=MyDb
FOR /F "usebackq tokens=1" %%i IN (`sqlcmd -S %server% -d %db% -w200 -h-1 -E -Q "set nocount on; select REPORTING_DATE FROM dbo.CURRENT_REPORTING_DATE"`) DO set REPORTING_DATE=%%i
ECHO The Reporting Date is %REPORTING_DATE%
But when I try to fully qualify the path to sqlcmd...
SET sqlcmdexe="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" SET server=MyServer SET db=MyDb
FOR /F "usebackq tokens=1" %%i IN (` %sqlcmdexe% -S %server% -d %db%
-w200 -h-1 -E -Q "set nocount on; select REPORTING_DATE FROM dbo.CURRENT_REPORTING_DATE"`) DO set REPORTING_DATE=%%i ECHO The Reporting Date is %REPORTING_DATE%
I get the error:
The system cannot find the path specified.
...presumably because of the spaces in the folder name.
How do I change the path to a tilde path (w/o spaces) or better yet, quote it so that this statement executes properly?
Note that there is a backwards tic before %sqlcmdexe% , not sure why I don't see it, at least in IE6. Yes, 6!
How do I change the path to a tilde path (w/o spaces)
As I don't have sqlcmd.exe installed, I use a different example. See for example this:
@echo off
set sqlcmdexe=C:\Program Files\Internet Explorer\Connection Wizard\icwconn2.exe
echo %sqlcmdexe%
for /f "tokens=*" %%a in ("%sqlcmdexe%") do set sqlcmdexe=%%~sa
echo %sqlcmdexe%
Run on my system, the output is:
C:\temp>envtest
C:\Program Files\Internet Explorer\Connection Wizard\icwconn2.exe
C:\PROGRA~1\INTERN~1\CONNEC~1\icwconn2.exe
But I don't know if this solves your problem.