Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: batch file to run a query

    Dear All,

    i need to put a query in a batch file to be easy to use by the user.
    jusy only double click on it and it will work.

    can any one help?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Checkout OSQL.EXE in books on line, that should give you what you need.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Dec 2004
    Posts
    47
    'ISQL' as well as 'OSQL' will do. ISQL is DB-Library applications & OSQL is ODBC oreinted....

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    OSQL is OLEDB oriented.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Dec 2004
    Posts
    47
    OLEDB is a successor to ODBC.... Such online command tools should be downward compatible... thats why its said ODBC oriented....

    http://db.ittoolbox.com/documents/document.asp?i=2412

    If you check Sql Server Books Online for OSQL u could see the below infrm....

    "The osql utility uses the ODBC database application programming interface (API). It is a replacement for the isql command prompt utility based on the DB-Library API. "

  6. #6
    Join Date
    Sep 2004
    Posts
    15
    But how do you assign the result of query to a DOS variable?

    for example, how do you assign the result of this query (select top 1 name from employee where empname = 'John') to a DOS variable in a batch program? I read that the use of EXIT with osql returns the result but when I tried to use it, it gave some errors. Does anyone has an exmaple?

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    I think that only works for integers;
    "You can use the result of a SELECT statement as the return value from isql. The first column of the first result row is converted to a 4-byte integer (long). MS-DOS passes the low byte to the parent process or operating-system error level. "

    If it's processing strings you want, I guess you'll need to use an output file and read the file from the batchfile (and delete it afterwards).

  8. #8
    Join Date
    Sep 2004
    Posts
    15
    How do you do the dos variable assignment if the select is changed to (select count(*) from employee where empname = 'John'). In this case the result will be integer.

    Say, I want to check if an employee already exists in a table.

    set /p emp='osql -S DBServ -U sa -P passwd -d master -Q "EXIT(select count(*) from employee where empname = 'John')"'
    if "%emp%"=="1" goto :EmpFound

    It gives me an error - "count(*) was unexpected at this time".

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Depending on your tolerance for pain, you can get assign SQL result set values to environment variables, but there are better answers. Look at Perl or KIX for examples.

    -PatP

  10. #10
    Join Date
    Sep 2004
    Posts
    15
    I am just trying to modify some pre-existing dos batch scripts and can't use perl.
    I am surprised that a simple variable assignment in dos batch script (from osql single value result) is such a complicated thing!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This will cause somebody to whirl in their grave, but consider this:
    Code:
    echo servername is %sn%
    osql -E -S. -Q "DECLARE @c VARCHAR(1000) SELECT @c = 'SET sn=' + @@servername PRINT @c" >c:\temp\sqlvoodo.bat
    call c:\temp\sqlvoodoo.bat
    echo servername is %sn%
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •