Solved

OSQL results output to a batch file variable

Posted on 2009-04-01
13
2,509 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

786 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