• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2817
  • Last Modified:

OSQL results output to a batch file variable

Hi,

I'm returning a single value from a OSQL query in a batch file. My requirement is to assign the returned value to a batch file variable, please let me know how.

Thanks in advance.
0
erwaga
Asked:
erwaga
  • 7
  • 6
1 Solution
 
oBdACommented:
"for /f" can do that; example (enter "help for" for details):
for /f "delims=" %%a in ('osql.exe ...') do set SomeVar=%%a
What you get out of this depends on the *exact* output of the command. To retrieve a special line or a special token.
Please provide a complete sample output of the osql command you're running.
0
 
erwagaAuthor Commented:
the output is a server IP address. E.g - 10.0.224.31

output;
        ----------------

        ----------------

        ----------------

        ----------------

        ----------------

        ----------------

        ----------------

        ----------------

        ----------------

        ----------------
10.0.224.31
0
 
oBdACommented:
Is that the actual output? The "problem" is to identify the exact line and token you're interested in.
Is the IP address of the server always starting with "10.", and is this line the only one containing "10."?
Then you can use the lines from the code snippet, otherwise please copy and paste the exact output of your osql command (note that you can copy the contents of a command window using the System menu).
REM *** Define your complete osql command here:
set OsqlCmd=osql.exe
for /f "tokens=1" %%a in ('%OsqlCmd% ^| find "10."') do set ServerIP=%%a

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
erwagaAuthor Commented:
Server name can be anything. Rather than searching the results is there anyway the output of OSQL can be stored in a variable?
0
 
oBdACommented:
Yes, but as I've said before, how to do that in such a way that you can do something with the results depends on the exact output of the command you're running, and for that, I'd need a sample output of your command.
The script below will simply put the complete osql output (except for the CRLF) into the variable osqloutput:
@echo off
setlocal enabledelayedexpansion
set OsqlCmd=osql.exe
set OsqlOutput=
for /f "tokens=1" %%a in ('%OsqlCmd%') do set OsqlOutput=!OsqlOutput!%%a

Open in new window

0
 
erwagaAuthor Commented:
Okay, I want to find DSServ prefixed result only from osql output.
0
 
oBdACommented:
Try the script below; if it still doesn't fit your needs, you *have* to provide a complete copy and paste of the osql command's output.

REM *** Define your complete osql command here:
set OsqlCmd=osql.exe
for /f "delims=" %%a in ('%OsqlCmd% ^| find /i "DSServ"') do set OsqlOutput=%%a

Open in new window

0
 
erwagaAuthor Commented:
The output is listed below; I want  DSServ prefixed text without the prefix (without DSServ) to be assigned to batch file variable,


1> 2> 3> 4> 5>                                                                                
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
            
 ------------------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----
 DSServ10.0.224.50\dctr                                                      
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
            

(1 row affected)



0
 
oBdACommented:
That should do the trick:
@echo off
REM *** Define your complete osql command here:
set OsqlCmd=osql.exe
for /f "tokens=1" %%a in ('%OsqlCmd% ^| find /i "DSServ"') do set OsqlOutput=%%a
set OsqlOutput=%OsqlOutput:DSServ=%
echo OSQL result: [%OsqlOutput%]

Open in new window

0
 
erwagaAuthor Commented:
I got the below result;

OSQL result:[DSServ=]

The result didn't have IP address included, and what I'm expecting is just 10.0.224.50\dctr assigned to a variable.
0
 
oBdACommented:
It works just fine here if I put your output into a text file and use
type test.txt
as OsqlCmd in order to reproduce your output.
"for /f" can't find a line containing "DSServ" in the osql output; did you adjust the OsqlCmd variable to contain your command?
If so, please post the complete output of the script below (after adjusting the OsqlCmd variable):
@echo off
REM *** Define your complete osql command here:
set OsqlCmd=osql.exe
echo OSQL command: %osql%
echo OSQL original output:
%OsqlCmd%
echo Retrieving DSServ value from OSQL output ...
for /f "tokens=1" %%a in ('%OsqlCmd% ^| find /i "DSServ"') do set OsqlOutput=%%a
set OsqlOutput=%OsqlOutput:DSServ=%
echo OSQL result: [%OsqlOutput%]

Open in new window

0
 
erwagaAuthor Commented:
Still I didn't get the expected results, find full batch code here;

@echo off
osql -E -d <<DATABASE NAME>> -S <<SERVER NAME>> -i <<SQL FILE NAME>>
set OsqlCmd=osql.exe
echo OSQL command: %osql%
echo OSQL original output:
%OsqlCmd%
echo Retrieving DSServ value from OSQL output ...
for /f "tokens=1" %%a in ('%OsqlCmd% ^| find /i "DSServ"') do set OsqlOutput=%%a
set OsqlOutput=%OsqlOutput:DSServ=%
echo OSQL result: [%OsqlOutput%]
pause


Theoretically  DSServ prefixed value should be in the output. Here is the output I got;


1> 2> 3> 4> 5>


























 -------------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ---------------------------------------------------------------
 DSServ10.0.224.50\dctr



























(1 row affected)
OSQL command:
OSQL original output:
usage: osql              [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w columnwidth]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-L list servers]      [-c cmdend]            [-D ODBC DSN name]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-n remove numbering]  [-m errorlevel]
  [-r msgs to stderr]    [-V severitylevel]
  [-i inputfile]         [-o outputfile]
  [-p print statistics]  [-b On error batch abort]
  [-X[1] disable commands [and exit with warning]]
  [-O use Old ISQL behavior disables the following]
      <EOF> batch processing
      Auto console width scaling
      Wide messages
      default errorlevel is -1 vs 1
  [-? show syntax summary]
Retrieving DSServ value from OSQL output ...
OSQL result: []
Press any key to continue . . .
0
 
oBdACommented:
As described in the script comment, you need to set the OsqlCmd variable to the osql command you're actually using:
@echo off
REM *** Define your complete osql command here:
set OsqlCmd=osql.exe -E -d <<DATABASE NAME>> -S <<SERVER NAME>> -i <<SQL FILE NAME>>
echo OSQL command: %OsqlCmd%
echo OSQL original output:
%OsqlCmd%
echo Retrieving DSServ value from OSQL output ...
for /f "tokens=1" %%a in ('%OsqlCmd% ^| find /i "DSServ"') do set OsqlOutput=%%a
set OsqlOutput=%OsqlOutput:DSServ=%
echo OSQL result: [%OsqlOutput%]

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now