Post reply

getting a value from sql server into dos variable

  • July 3, 2003 at 4:34 pm

    #159741

    Hi

    Does anyone know how I can get a dos variable set up by a sql in sql server via

    the osql command ?

    Thanks in advance

    AW

  • July 3, 2003 at 6:02 pm

    #464143

    Somewhat convoluted, but here's one way:

    FOR /F "tokens=1-2" %i IN ('osql -E -Q"select 'SET DBNAME=' + db_name(2)"') DO @IF /I %i EQU SET SET %j

    echo %DBNAME%

    The OSQL Query "select 'SET DBNAME=' + db_name(2)" returns a DOS command string: SET DBNAME=tempdb

    The FOR command (FOR /F "tokens=1-2" %i) grabs the first 2 tokens of each result line and assigns it to variables %i and %j

    The IF component (@IF /I %i EQU SET) only looks for result lines beginning with the word "SET"

    The last bit (SET %j) executes the DOS SET command


    Cheers,
    - Mark

  • July 4, 2003 at 2:33 am

    #464168

    Thank you very much McCork . I'll have fun

    with this ! I'll let you know how I get on

  • July 4, 2003 at 3:08 am

    #464170

    Hi

    I've run this but ECHO %DBNAME% just returns

    %DBNAME%.

    Can you help?

    Thanks

  • July 4, 2003 at 7:30 am

    #464189

    It works ok for me. I can only suggest to break it down into components. ie. Does the OSQL command by itself return expected results? If so, do you get results printed if you replace the IF bit with: @echo %i %j


    Cheers,
    - Mark

  • July 4, 2003 at 5:17 pm

    #464209

    Hi

    Thanks for your help . I'll be patient and break it down as you suggest.

  • July 7, 2003 at 4:19 am

    #464251

    I think somthing like this may work:

    osql -E -S %server% -d %metadata% -Q "EXIT(select completed = case Completed when 'SUCCESS' then 999 else -999 end from Monitor_Progress where Thread=%thread% and [Table]='%tablename%')"

    set completed=%errorlevel%

    Completed ends up with 999 or -999

    Hope this helps

    Andy

  • July 7, 2003 at 7:09 am

    #464289

    Another thing to watch out for - when using the DOS FOR cmd within a BAT file, the variables of the FOR cmd require double %%.

Viewing 8 posts - 1 through 7 (of 7 total)

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