Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    5

    Post Unanswered: Environment Variables Within SQL

    Is there some way I can access system environment variables within a SQL script? For example, if I have a script "foo.sql" that I'm calling from isql, I want to be able to substitute <hostname> with an env variable I set at the command line:

    foo.sql:

    update tblFoo set HostName = <hostname>

    Thanks.

    Terence

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Environment Variables Within SQL

    Q1 Is there some way I can access system environment variables within a SQL script?

    A1 Yes.

    Note: The following is in regard to osql (however it should hold true for isql as well)

    If issuing queries from osql, (also should work using batch files), one may use environment variables i.e.( %variablename% ) directly. For example:

    Define the following two environment variables:
    Set TargetDB = Pubs
    Set TargetTable = Authors

    Then run the following example (replace SqlServer with your SqlServer instance name before running) from the command prompt:

    Example:

    osql SSqlServer -E -dPubs -q"exit(Select Au_LName from %TargetDB%..%TargetTable% Order By Au_LName Go Select Count(*) As 'AuthorsCount' From %TargetTable%)"

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Note that DBA's suggestion works because all the variables have been substituted PRIOR to running osql.exe. This method won't work with a script. To my knowledge there is no way to reference environment variables from SQL server.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Oct 2002
    Posts
    369
    RE:
    Note that DBA's suggestion works because all the variables have been substituted PRIOR to running osql.exe. This method won't work with a script. To my knowledge there is no way to reference environment variables from SQL server.

    A good point. If the simple approach demonstrated is unworkable for the requirements at hand; another approach to consider may be to create one or more stored procedures which may be executed such that the desired results may be achieved indirectly.

    For example, several utility procs may be created which shell out to the OS and execute OS commands directly or that call short VB scripts to gather, set, and / or otherwise manipulate the environment variables as required.

Posting Permissions

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