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.

TerenceQ1 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%)"|||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.|||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.