Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Posts
    115

    Question Unanswered: How can I execute SQL query from DOS with parameters?

    I believe all of us use SQL Server Management Studio to execute query. As I encountered serious problem with this method, due to typo error by accident, I am wondering if we can run the .sql file from dos and supply parameters with it.

    Possible?
    Last edited by tingshen; 02-08-07 at 07:48.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    write a batch fire Test.bat as below (here I am using a trusted connection change it accordingly)
    Code:
    osql -E -Q%1
    then call it with any sql... like

    C:>Test "select * from DBName..TableName"

    there could be many other variations of this. check BOL

  3. #3
    Join Date
    Oct 2006
    Posts
    115
    what shall I do if I have a .sql files which consist of 1000 lines of queries? can it be done in the same way? can pass parameter as variable in the query?

    is osql only accepting 1 command each time?

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if you are using 2005, you should use sqlcmd.exe. osql.exe is deprecated.

    with sqlcmd, you can use parameterized queries where the parameters are environment variables. it's a very powerful technique.

    http://msdn2.microsoft.com/en-us/library/ms170207.aspx

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Eg. using sel2.sql
    Code:
    C:\tmp>type sel2.sql
    select dbid,status from sysdatabases
    where name=@name
       or status=@status
    order by dbid
    go
    create a batch file to supply parameters e.g.
    Code:
    C:\tmp>type b1.bat
    @echo off
    echo declare @name varchar(30),@status int >workfile.sql
    echo select @name="%1",@status=%2 >>workfile.sql
    type sel2.sql >>workfile.sql
    osql -n -Ume -Pmypass -Smydb -i workfile.sql
    del workfile.sql
    And run it e.g.
    Code:
    C:\tmp>b1 tempdb 24
     dbid   status
     ------ -----------
          1          24
          2          12
    
    (2 rows affected)
    Last edited by pdreyer; 02-09-07 at 03:52.

Posting Permissions

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