Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Question Unanswered: how to pass a input value to a script using "osql"

    I want to run a sql script that creates some views using "osql". The views in turn refer to user defined functions which need a two-part name reference(MyUser.MyFunction). Is there a way to do this without hardcoding the value of the "MyUser" in the script?

    Example..

    I want to run a script called myscript.sql using OSQL. The file myscript.sql looks like this..

    CREATE view V1
    AS
    SELECT *, UserOwner1.MyFunction1(5)
    FROM MyTable
    go

    CREATE view V11111
    AS
    SELECT *, UserOwner1.ScalarFunction11111(7)
    FROM MyTable
    go

    -----

    I do not want to hardcode the username (UserOwner1) since it varies from database to database. So when I run this script from command prompt using "osql" I want the ability to supply the value for the username (UserOwner1) which owns the user defined function. How to do this?

    This problem would go away if SQLServer 2000 was not so adamant about requiring two part reference to a user defined function.

    Thanks!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    As per your last statement, - be prepared for a rude awakening when Yukon comes out!!!

    But when you issue your OSQL from command prompt, - if you logon as the owner of the object, you can reference all objects (except for functions) with one-part naming convention. You can't avoid having to reference functions with 2-part name.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Sep 2004
    Posts
    15
    Thats the problem since I want to input the "owner of the object" as a command line input value through the "osql". This value will be used to login to the database and run the script. So the owner reference in the function can not be hard-coded and will have to use the input value passed to the script. So how can this be done while using "osql".

  4. #4
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    I experimented with a couple of approaches and found an approach Im not proud of, but it works. You have to build your t-sql script on the fly (possible in the .BAT file) and then execute that file with OSQL.

    For example, if the SQL I wish to execute is something like:
    SELECT RegionName from Regions where RegionCode = @RegionCode

    This statement(s) goes into a file, for this example Ill call it paramquery.sql

    Use Environment variables to build the first part:
    Echo declare @RegionCode varchar(20) > fred.sql
    Echo set @RegionCode = %mybatvar% >> fred.sql <- set from command line
    Type paramquery.sql >> fred.sql

    Then execute the file you built:
    OSQL S myserver E d mydatabase i fred.sql

    You were looking at using the passed variable as part of the SQL itself. You could use this approach to build a SQL command variable and then EXEC the variable (aka. Dynamic SQL).

    Echo declare @cmd varchar(200) > fred.sql
    Echo set @cmd = select %mybatvar%.myfunction() from mytable' >> fred.sql
    echo exec @cmd >> fred.sql
    OSQL S myserver E d mydatabase i fred.sql
    Fred Prose

  5. #5
    Join Date
    Sep 2004
    Posts
    15
    Thanks for the example.

Posting Permissions

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