Assigning output of a query to a variable

Question

Assigning output of a query to a variable

viksds15

Old Hand

Points: 360
More actions
December 1, 2008 at 1:50 am

#99378

Hi,
Is there a possibility of assigning the output of a sql query to a variable in a batch file.
for ex:
sqlcmd -E -Q"select flag from table_A"
I want the output of the query to a variable in a batch script.
Is this possible.
Thanks,
Victor

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

eharper SSC Eights! Points: 802 More actions · Answer 1

The way I done this in the past to direct the output of SQLCMD to a text file, then read the value from the text file back into a variable:

sqlcmd -E -Q"set nocount on;select 'test1'" -h -1 > tmp.txt

set /P myVar= < tmp.txt

echo %myVar%

del tmp.txt

Notice the use of the -h -1 flag (which tells SQLCMD to supress column headings in the output) and NOCOUNT within in the SQL command (to supress the count of rows affected).

Whilst not directly related to answering your question, I'll add that you should probably add some error handling - but it's worth knowing that, by default, SQLCMD will not set ERRORLEVEL to a non-zero value if the SQL command fails. To do this, you need to supply the -b flag.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Use output parameters. They work a little like this:

--create a procedure

CREATE PROCEDURE dbo.MyProc

@ID int OUTPUT

AS

SELECT ID FROM schema.Table

GO

--now you can call the proc like this

DECLARE @LocalId int

EXEC dbo.MyProc @ID = @LocalID OUTPUT

SELECT @LocalID

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Oops. Missed that this was SQLCMD

That's OK. You can use SQLCMD variables the same way, still using OUTPUT parameters. For more on SQL Command variables, this is BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/793495ca-cfc9-498d-8276-c44a5d09a92c.htm

Or online go here: http://msdn.microsoft.com/en-us/library/ms188714.aspx

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

eharper SSC Eights! Points: 802 More actions · Answer 4

Grant, do you have a working example of how to assign a value back to a CMD variable using SQLCMD that you're able to post here?

It seems like something one ought to be able to do, but I've never worked out how - the SQLCMD docs only give examples of using CMD variables as input.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

Output to what? I mean what am I outputing to? Sqlcmd can just output to a file pretty easily if that's what you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

eharper SSC Eights! Points: 802 More actions · Answer 6

I think perhaps we're talking at cross purposes.

My understanding is that the original question asked how to assign a single row, single column result set from a SQLCMD query run in a DOS batch file to a DOS variable in the same batch file. This is what I mean by a CMD variable.

I showed a way to do this using a temporary file, but I understood your second post on the thread to mean that you know how to do this without using a file (or that the docs explain how to do so). It's an example of this I was asking to see.

Apologies if I've misunderstood.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

No, no, no apoloogies. If there's any misunderstanding, it's on my side. I think your solution is likely the only one. I've never needed to capture an output from sqlcmd to a DOS variable, the other way around is most common.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

viksds15 Old Hand Points: 360 More actions · Answer 8

Hi,

Thanks a lot for the code...

It worked fine... and exactly what I wanted.

Thanks,

Victor

distant star SSC Enthusiast Points: 176 More actions · Answer 9

Hello,

I am working on an automation script using detach-attach method for which I need help of you colleagues.

Following is the plan which has to be executed using a single script:

1. Check for the source and target SQL Server Versions (as source can be SQL 2000 or 2005)

2. List and store the current path of Source DB files (data and log files of say TEST db) prior to detaching the db.

2. Detach the db and zip the files prior to transfering over the network.

3. Once the Zipped files are copied to the target Server on a different host, unzip the files and attach the DB.

Please help me in this issue.

Thanks in advance,

Distantt Star

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

distant star (2/18/2009)
Hello,
I am working on an automation script using detach-attach method for which I need help of you colleagues.
Following is the plan which has to be executed using a single script:
1. Check for the source and target SQL Server Versions (as source can be SQL 2000 or 2005)
2. List and store the current path of Source DB files (data and log files of say TEST db) prior to detaching the db.
2. Detach the db and zip the files prior to transfering over the network.
3. Once the Zipped files are copied to the target Server on a different host, unzip the files and attach the DB.
Please help me in this issue.
Thanks in advance,
Distantt Star

First, I'd suggest you post this as a new question. You'll get a lot more eyeballs looking at it. Second, what's the problem. It sounds like you've got a good plan. Are you stuck somewhere?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLEngine SSCommitted Points: 1633 More actions · Answer 11

I am not able to see the code. I need to do exactly that, capture the output of SQLCMD to the variable in the batch file.

How did you end up doing this?

Thanks

quickdraw SSC Enthusiast Points: 151 More actions · Answer 12

Save this to a text file called test.bat then execute on the command line:

@echo off

FOR /F "usebackq tokens=1,2,3 delims=~" %%i IN (`sqlcmd -w200 -h-1 -E -Q"set nocount on; select convert(varchar(10),getdate()) + '~' + 'someval2' + '~' + 'someval3'"`) DO (

set somevar1=%%i

set somevar2=%%j

set somevar3=%%k

)

ECHO %somevar1%

ECHO %somevar2%

ECHO %somevar3%