Results 1 to 3 of 3

Thread: OSQL Question

  1. #1
    Join Date
    Sep 2003
    Posts
    49

    Unanswered: OSQL Question

    I created a sql script that transfers data from my test system to production adn vice versa. I would like to use OSQL and be able to pass parameters to the SQL script.

    Example: param 1 = test; param 2 = production.

    Can anyone tell me if this is possible and how to do it?

    Thanks in advance for all the help.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You can't directly pass parameters as part of the OSQL command-line utility, but you have several alternatives for solving your problem.

    As stated in Winnet mag article :
    OSQL won't let you pass in parameters, but you can use the sp_executesql stored procedure, which can process parameterized SQL. (For detailed information about sp_executesql, see SQL Server Books Online—BOL.) The following example lets you pass parameters into a T-SQL command but doesn't completely address how to pass a parameter as part of an OSQL command-line session (remember that you must issue the OSQL command from a command prompt):

    osql -E -MyServer -Q "EXECUTE sp_executesql N'SELECT *
    FROM northwind..
    orders WHERE OrderId = @OrderId' ,N'@OrderId int' ,@OrderId = 10248"
    Another solution relies on the power of Windows to handle the parameterization for you. For DBAs who aren't familiar with Windows-level command-file processing, a batch file is a text file that has a .bat extension. Windows treats batch files as executables that run in the Command Prompt environment. You can think of batch files as mini programs that Windows runs.

    You can simply create a file called SQLVariableBatch.bat, and put the following text in it:

    osql -E -MyServer -Q "SELECT *
    FROM northwind..orders WHERE OrderId = %1"
    From the directory where you saved the .bat file, issue the following command from a command prompt window:

    SQLVariableBatch 10248
    When running this command, Windows will replace the %1 in the SQLVariableBatch.bat file with what comes after the batch file's name in the command line—in this case, 10248. This is a simple example of batch processing in Windows; to learn more about batch files, see the Windows Help files.

    You can also use Windows Scripting Host (WSH) to manage the parameterization. Using WSH for scripting and batch processing is much more flexible and powerful than using simple Windows batch files. I'm not a WSH expert, so I don't include an example of this solution, but I wanted to note that the option exists. For information about using WSH, see the MSDN Web site for Windows Script at

    http://msdn.microsoft.com/library/de...entid=28001169 .
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Sep 2003
    Posts
    49
    Thanks for your help. I will try your suggestions and see if I can figure it out.

Posting Permissions

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