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.

environment variables

Well, having only one disk partition, and only one directory, and running only one application probably ROCKS too, at least for that one application :)
But if you're unfortunate enough to be required to use multiple applications, you might be saddened by certain aspects of environment variables, such as them living in one globally competitive namespace, or them being not covered by the NT security model (AFAIK).
But I grant you, that they solve the portability problem with package configurations, so I was happy to use them nonetheless.
I'd use environment variables (or any other hack in all likelihood), if I could find a solution for the lack of reusability in SSIS -- that is driving us to move as much code as possible outside of SSIS for maintainability. :(

We're always looking for product feedback; what are the aspects of your packages that you wish you could re-use? (Also, it sounds like you're replying to another thread... Was this post misplaced?)

|||

Cim Ryan wrote:

We're always looking for product feedback; what are the aspects of your packages that you wish you could re-use? (Also, it sounds like you're replying to another thread... Was this post misplaced?)

Cim/Perry,
I'm jumping in on your thread here, sorry about that.

Here's a list of things that could, IMO, be made reusable:
-Data-Flows (the most obvious one)
-Tasks (e.g. an Execute SQL task that calls a auditing sproc)
-A sequence container that carries out a single unit of work
-A For/ForEach Loop (including all tasks within it)
-A configured component
-A group of configured components (http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx)
The important point to make is that they shouldn't just be made reusable in the same package, they should be reusable in ANY package. In that sense, each one of them could be a deployable object. Currently the only deployable object is a package - why should that be the case? Why not deploy (for example) a task, or even a component, and then use that in any package?

-Jamie

|||To add to Jamie's excellent list at the lower level,
expressions
I say this because copying and pasting transformations from one Derived Column task to another is tedious, and I tend to believe that copy&paste leads to poor maintainability and scalability.

|||Also, the Aggregate defaults most columns to Group By, it usually misses one out of a long list, and for some reason it always misses the ErrorCode and ErrorNumber, when they are added at the bottom of the output column list from a Lookup Error, so when you finish checking all the columns to group by, you track the Validation error back and find that the Aggregate left those columns unspecified -- which means it is in an invalid state -- so you have to manually set those to Group By.
Actually, sounds more like a bug than an RFE to me, but I don't care what list it goes on if it gets fixed :)
sql

Environment Variables

Hi everyone...

I'm trying to create a database with the parent directory being a environment variable,

something like this:

Code Snippet

CREATE DATABASE mydatabase
ON
PRIMARY(NAME = myDataBase,
FILENAME = '%PARENTDIRECTORY%\mydatabase.mdf',

the problem is I don't know to get an environment variable in transact SQL...

I know that in C# we can get it with %PARENTDIRECTORY%...

Thanx in advance

The following batch may help you...

Code Snippet

Create Table #Result

(

Data varchar(8000)

);

Insert Into #Result

Exec master..xp_cmdshell 'echo %TEMP%';

Declare @.ParentDirectory as nvarchar(256);

Select Top 1 @.ParentDirectory = Data From #result;

Select @.ParentDirectory = @.ParentDirectory + '\mydatabase.mdf'

Exec ('CREATE DATABASE mydatabase

ON

PRIMARY(NAME = myDataBase,

FILENAME = ''' + @.ParentDirectory + ''')')

Drop table #result

|||Thanx Manivannan the code above does the job, but doesn't the xp_cmdshell work only for XP users?

|||

sqlclr's udf can imprement below,

This assembly's permission need to 'EXTERNAL_ACCESS'.

Usage:

Code Snippet

select dbo.GetEnvironmentVariable('temp');

C# Source file:

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.None)]
public static SqlString GetEnvironmentVariable(string variable)
{
string value = Environment.GetEnvironmentVariable(variable);
return new SqlString(value);
}
};

Enumeration variables??

Is it possible to create variables of type enum in PL/SQL?
In VB.NET it would be something like e.g.: enum seasons {Spring, Summer, Autumn, Winter}
Thx.Originally posted by OracleDev
Is it possible to create variables of type enum in PL/SQL?
In VB.NET it would be something like e.g.: enum seasons {Spring, Summer, Autumn, Winter}

Thx.
No, PL/SQL does not have such a type.|||I feared this, because I couldn't find info about it.

Thx anyway.