Setting a variable from the output of a command (sqlcmd)

[jpohl]

I am attempting to do something releatively simple, but am having great difficulty.

I want to sent a variable (%OBJECT_VER_DB%) from the output of a command. The command happens to be the output of a query from SQL server. If I run the following command, it returns a string:
sqlcmd -S %DBSERVER%\%DBINSTANCE%,%DBPORT% -d %DBNAME% -U %DBUSER% -P %DBPASS% -i ..\admin\sql\%DBTYPE%\GET_OBJECT_VERSION.sql -b -v ObjectType='FUNCTION' ObjectName='%OBJECT_NAME%'

I thought I had found success with the following line in my batch script:
IF %~1 EQU functions (set "OBJECT_VER_DB=|sqlcmd -S %DBSERVER%\%DBINSTANCE%,%DBPORT% -d %DBNAME% -U %DBUSER% -P %DBPASS% -i ..\admin\sql\%DBTYPE%\GET_OBJECT_VERSION.sql -b -v ObjectType='FUNCTION' ObjectName='%OBJECT_NAME%'")

When ever I perform an echo (echo %OBJECT_VER_DB%) immediately after this, I get the correct result on the screen.

However, if I then try to use the variable later in a comparison, I get an error.

Here is the line I am trying to run:
IF %OBJECT_VER_DB% EQU JP (echo "OBJECT_VER_DB was not set")

Here is the error I get:
| was unexpected at this time.


Any ideas?

[Ed Dyreen]

'
I don't use sqlcmd, so I have no way of verifying it supports quotes, if it does, you should quote your arguments if they contain spaces.

Code: Select all

@echo off &setlocal enableDelayedExpansion

set ^"$CMD=sqlcmd ^
-S %DBSERVER%\%DBINSTANCE%,%DBPORT% ^
-d %DBNAME% ^
-U %DBUSER% ^
-P %DBPASS% ^
-i ..\admin\sql\%DBTYPE%\GET_OBJECT_VERSION.sql ^
-b ^
-v ObjectType='FUNCTION' ObjectName='%OBJECT_NAME%' ^
"

for /f "usebackq delims=" %%? in ( `!$CMD!` ) do echo.?=%%?_

untested !

[jpohl]

I attempted this code. And it did not work. I changed the DO part of the FOR loop to be:
do SET OBJECT_VER_DB=%%?_

and then I did a echo %OBJECT_VER_DB%, but the variable did not change at all.

[Ed Dyreen]

'
Try to construct a valid SQL command, you can redirect the output to a file like

Code: Select all

sqlcmd "parameters" > file.TXT

The for is only to catch this output.

Code: Select all

for /f "usebackq delims=" %%? in ( `echo.this could be the program's output` ) do echo.?=%%?_

You could then do something like

Code: Select all

for /f "usebackq delims=" %%? in ( `echo.this could be the program's output`) do set "variable=%%?"
echo.variable=%variable%_

[jpohl]

I got it working by outputing to a file and then reading it. There is probably a way to direct it straight to a variable, but I've spent too much time on it already.

Here is what I've done:
SET OBJECT_VER_DB=NULL

::Write the Object Version to a temporary file
IF %~1 EQU tables (sqlcmd -S %DBSERVER%\%DBINSTANCE%,%DBPORT% -d %DBNAME% -U %DBUSER% -P %DBPASS% -i ..\admin\sql\%DBTYPE%\GET_OBJECT_VERSION.sql -b -v ObjectType='TABLE' ObjectName='%OBJECT_NAME%' > object_ver.tmp)

::Read the Object Version into a variable from the temporary file
IF EXIST object_ver.tmp (FOR /F %%V IN (object_ver.tmp) DO (SET OBJECT_VER_DB=%%V))

echo %OBJECT_VER_DB%

IF %OBJECT_VER_DB% EQU NULL (echo "OBJECT_VER_DB was not set")

::Delete the temporary file
del object_ver.tmp

[orange_batch]

I am attempting to do something releatively simple, but am having great difficulty.

I want to sent a variable (%OBJECT_VER_DB%) from the output of a command. The command happens to be the output of a query from SQL server. If I run the following command, it returns a string:
sqlcmd -S %DBSERVER%\%DBINSTANCE%,%DBPORT% -d %DBNAME% -U %DBUSER% -P %DBPASS% -i ..\admin\sql\%DBTYPE%\GET_OBJECT_VERSION.sql -b -v ObjectType='FUNCTION' ObjectName='%OBJECT_NAME%'

I thought I had found success with the following line in my batch script:
IF %~1 EQU functions (set "OBJECT_VER_DB=|sqlcmd -S %DBSERVER%\%DBINSTANCE%,%DBPORT% -d %DBNAME% -U %DBUSER% -P %DBPASS% -i ..\admin\sql\%DBTYPE%\GET_OBJECT_VERSION.sql -b -v ObjectType='FUNCTION' ObjectName='%OBJECT_NAME%'")

When ever I perform an echo (echo %OBJECT_VER_DB%) immediately after this, I get the correct result on the screen.

However, if I then try to use the variable later in a comparison, I get an error.

Here is the line I am trying to run:
IF %OBJECT_VER_DB% EQU JP (echo "OBJECT_VER_DB was not set")

Here is the error I get:
| was unexpected at this time.


Any ideas?

You're trying to "reverse pipe" (impossible) a command into set (impossible, except on /p). There are no objects in DOS batch, only variables containing static data, so you either store the sqlcmd code line into a variable or you store the result of the sqlcmd call using for.

This will store the result of the "sqlcmd..." at the time of processing to %OBJECT_VER_DB%. You would use this to compare the result in batch. Since your sqlcmd contains single quote characters, usebackq would be smart for processing the for command as such:

Code: Select all

IF %~1 EQU functions for /f "usebackq delims=" %%a in (`"sqlcmd -S %DBSERVER%\%DBINSTANCE%,%DBPORT% -d %DBNAME% -U %DBUSER% -P %DBPASS% -i ..\admin\sql\%DBTYPE%\GET_OBJECT_VERSION.sql -b -v ObjectType='FUNCTION' ObjectName='%OBJECT_NAME%'"`) do set "OBJECT_VER_DB=%%a"

This will store the "sqlcmd..." to %OBJECT_VER_DB%. Afterward, you can write %OBJECT_VER_DB% by itself on a line in batch and it will display the result of the command as expected.

Code: Select all

IF %~1 EQU functions set "OBJECT_VER_DB=sqlcmd -S %DBSERVER%\%DBINSTANCE%,%DBPORT% -d %DBNAME% -U %DBUSER% -P %DBPASS% -i ..\admin\sql\%DBTYPE%\GET_OBJECT_VERSION.sql -b -v ObjectType='FUNCTION' ObjectName='%OBJECT_NAME%'"

If you need both, obviously change the second example's variable name to something like OBJECT_VER_DB_CMD.