how to return result of a query into a dos variable?

Question

how to return result of a query into a dos variable?

U

SSC Veteran

Points: 265
More actions
January 13, 2005 at 5:13 pm

#165742

I want to take an action based on the value of the data returned by a query inside a dos batch file. For example I want to see if a database "AddressDB" already exists before creating it. If it already exists I want the batch file to report errors using the echo statement.
I want to assign the result of the following osql command to a DOS variable and then check the value of that variable.
DOS_variable = osql -S %server_name% -U sa -P %sa_pwd% -d master -Q "select name from master..sysdatabases where name = 'AddressDB'"
How to do it?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Have a look at osql.exe in Books On Line. Perhaps you can adapt EXIT or RAISERROR in some useful way.

eg DOS_variable = osql -S %server_name% -U sa -P %sa_pwd% -d master -Q "EXIT(select count(*) from master..sysdatabases where name = 'AddressDB')"

..should return either a 1 or a zero if no db exists..

/Kenneth

U SSC Veteran Points: 265 More actions · Answer 2

Thanks Kenneth.

I am having problem wrapping a dos script around it. Do you have an example of dos assignment?

Here is what I have tried to do but it gives an error.."count(*) was unexpected at this time"

---------------------------

set dirname=%1

set dbpath=%2

set dbname=%3

set dbpasswd=%4

set server_name=%5

set sa_pwd=%6

set database_exists=(osql -S %server_name% -U sa -P %sa_pwd% -d master -Q "EXIT(select count(*) from master..sysdatabases where name = %dbname%)")

if "%database_exists%"=="1" goto atabaseFound

goto end

atabaseFound

echo Can't create the database.

echo The database %dbname% already exists on this SQL Server.

:end

@echo on

---------------------

I am getting "count(*) was unexpected at this time" error.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

I believe that your outer parenthesis are the problem. Compare your script tot the previous answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

U SSC Veteran Points: 265 More actions · Answer 4

Jeff,

I tried it without the outer parenthesis but it gives exact same error. I also tried single quotes around osql but it also gives the same error. It is frustrating

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

save this as chkDB.cmd or similar

-- start file --

@echo off

osql -S%1 -E -Q"EXIT(select count(*) from master.dbo.sysdatabases where name = '%2')"

@echo on

IF %ERRORLEVEL%==0 ECHO Nope

IF %ERRORLEVEL%==1 ECHO Yes

-- end file --

Then from a promt say chkDB.cmd myServer myDB

You'll see that ERRORLEVEL contains 0 or 1, depending on the count returned from the select.

/Kenneth