saving output as variable

Question

I am working on a batch script and am trying to save part of the output of a command as a variable. My current script requires the user to enter a site code (e.g. 853), and it takes that code and uses it to query a sql database. The command I'm using is as follows:

sqlcmd -S"localhost\SQLONESOURCE" -Uusername -Pmypassword -dOneSource -Q"select AdmSiteID from [10.33.144.114].onesource.dbo.admsites where siteid='%sitecode%'"

When this command runs, I get the following output:
AdmSiteID ------------------------------------ AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE (1 rows affected)

I just want to take the AdmSiteID (AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE) and save that as a variable. It will always be in that format with the same number of characters and dashes.

Aacini · Accepted Answer · 2013-06-11 21:13:22Z

2

This batch code get the third line from your sqlcmd output:

for /F "skip=2" %%a in ('sqlcmd -S"localhost\SQLONESOURCE" -Uusername -Pmypassword -dOneSource -Q"select AdmSiteID from [10.33.144.114].onesource.dbo.admsites where siteid='%sitecode%'"') do (
   set thirdLine=%%a
   goto continue
)
:continue
echo %thirdLine%

answered Jun 11, 2013 at 21:13

Aacini

67.7k12 gold badges74 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2382144 Over a year ago

Yes, thank you! I don't know why i didn't see the "skip" argument in any of the documentation I was found for the for command.

Simon Catlin · Accepted Answer · 2013-06-11 21:04:36Z

The first thing to do is to ensure your SQLCMD output includes some sort of unique text that you can search for. For example, you could modify your query as follows:

"select 'FOO~' + AdmSiteID + '~' from [10.33.144.114].onesource.dbo.admsites where siteid='%sitecode%'"

Then write your SQLCMD output to a file by adding -o %OUTPUT_FILE% to your SQLCMD.EXE line, where %OUTPUT_FILE% has already been defined.

Then use a combination of for and findstr.exe to pull your file apart:

for /f "tokens=2 delims=~" %%D in ('%SystemRoot%\System32\findstr.exe /l /c:"FOO~" %OUTPUT_FILE%') do set YOUR_VARIABLE=%%D

This hasn't been tested, but I know the theory is sound. If it doesn't work, and you're struggling to fix it, let me know, and I'll spend more time on it.

Collectives™ on Stack Overflow

saving output as variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related