Solved

OSQL results output to a batch file variable

Posted on 2009-04-01
13
2,578 Views
Last Modified: 2012-05-06
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
Comment
Question by:erwaga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 85

Expert Comment

by:oBdA
ID: 24038200
"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
 

Author Comment

by:erwaga
ID: 24038284
the output is a server IP address. E.g - 10.0.224.31

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

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

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

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

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

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

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

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

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

        ----------------
10.0.224.31
0
 
LVL 85

Expert Comment

by:oBdA
ID: 24038371
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:erwaga
ID: 24038650
Server name can be anything. Rather than searching the results is there anyway the output of OSQL can be stored in a variable?
0
 
LVL 85

Expert Comment

by:oBdA
ID: 24038745
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
 

Author Comment

by:erwaga
ID: 24094744
Okay, I want to find DSServ prefixed result only from osql output.
0
 
LVL 85

Expert Comment

by:oBdA
ID: 24099653
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
 

Author Comment

by:erwaga
ID: 24104470
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
 
LVL 85

Expert Comment

by:oBdA
ID: 24104882
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
 

Author Comment

by:erwaga
ID: 24105009
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
 
LVL 85

Expert Comment

by:oBdA
ID: 24105114
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
 

Author Comment

by:erwaga
ID: 24105201
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
 
LVL 85

Accepted Solution

by:
oBdA earned 500 total points
ID: 24105274
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

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The following is a collection of cases for strange behaviour when using advanced techniques in DOS batch files. You should have some basic experience in batch "programming", as I'm assuming some knowledge and not further explain the basics. For some…
You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question