Solved

OSQL results output to a batch file variable

Posted on 2009-04-01
13
2,478 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
  • 7
  • 6
13 Comments
 
LVL 83

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 83

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
 

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 83

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 83

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 83

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 83

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 83

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Introduction: Recently, I got a requirement to zip all files individually with batch file script in Windows OS. I don't know much about scripting, but I searched Google and found a lot of examples and websites to complete my task. Finally, I was ab…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now