How to store SQL query result in a local variable of batch file?

Question

I am using following SQL query to get version number from database through a batchfile.

sqlcmd.exe -S localhost -d MyDB 
           -Q "set nocount on;Declare @VersionNumber nvarchar(128) set @VersionNumber = (select top 1 versionnumbe from Table_schemaversion order by Id desc)  print 'Your Previous Version is: ' + @VersionNumber "

This query is working fine, and producing the correct result.

But now I need to store this result into a local variable of the batch file. And I cannot use any external file here, I have to do this by the query written above, only.

How can I do this? Can anybody let me me about a solution to do this?

Thanks in advance.....

why dont that batch file read the version from an external file instead ? — Shamis Shukoor
– Shamis Shukoor, Commented Dec 4, 2012 at 6:04
i know it is possible by external file, but i have to do this without using any external file, ??? is it possible ?? — Rajesh
– Rajesh, Commented Dec 4, 2012 at 6:32
Cant you execute the query in the batch file and use the result — Shamis Shukoor
– Shamis Shukoor, Commented Dec 4, 2012 at 6:33

Rafay · Accepted Answer · 2012-12-05 07:34:56Z

4

You will have to write the result of query in a temp file and then read it from there.

sqlcmd  -S ".\SQLEXPRESS" -Q "select c1 from [a].[dbo].[t1] where c1='1'" -U sa -P wavetec -h -1 -o tempstorage.txt        
set /p varname=<tempstorage.txt
echo %varname%

-o: creates the file
-h -1: omits the header

Hope this is what you want!!

answered Dec 5, 2012 at 7:34

Rafay

6092 gold badges9 silver badges25 bronze badges

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

Comments

npocmaka · Accepted Answer · 2012-12-04 08:44:39Z

1

If the command result is a single line this will be enough:

FOR /F "usebackq" %%S IN (`sqlcmd.exe -S localhost -d MyDB  -Q "set nocount on;Declare @VersionNumber nvarchar^(128^) set @VersionNumber = ^(select top 1 versionnumbe from Table_schemaversion order by Id desc^)  print 'Your Previous Version is: ' + @VersionNumber "`) DO SET var=%%S

or

FOR /F  %%S IN ('sqlcmd.exe -S localhost -d MyDB  -Q "set nocount on;Declare @VersionNumber nvarchar^(128^) set @VersionNumber = ^(select top 1 versionnumbe from Table_schemaversion order by Id desc^)  print ^'Your Previous Version is: ^' + @VersionNumber "') DO SET var=%%S

answered Dec 4, 2012 at 8:44

npocmaka

57.8k19 gold badges162 silver badges199 bronze badges

3 Comments

Rajesh Over a year ago

i applied both the above commands but not able to print its result. I need to store its output in a variable, like set a = myversion number,

npocmaka Over a year ago

is there any error?Did you tried to print the var after that- > echo %var%

npocmaka Over a year ago

looks like a stuff printed by sqlcmd.exe.Is the new line mandatory?You can set a new line in FOR /F condition without harming the loop.

Collectives™ on Stack Overflow

How to store SQL query result in a local variable of batch file?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related