Environmental Variable

Does the setup for SQL also set the corresponding environmental variable, or
is this something that has to be done separately? If separately, which
environmental variable and how?
Thanks,
VictorHi ,
It is not at all required to set / create any environment variables before
SQL Server installation. The
installation program will do all the necessary path entries automatically.
Thanks
Hari
MCDBA
"Victor" <vmgoldberg__NO-_*SPAM*_-PLEASE@.earthlink.net> wrote in message
news:ohMvc.36767$zO3.10088@.newsread2.news.atl.earthlink.net...
> Does the setup for SQL also set the corresponding environmental variable,
or
> is this something that has to be done separately? If separately, which
> environmental variable and how?
> Thanks,
> Victor
>

Environmental Variable

Does the setup for SQL also set the corresponding environmental variable, or
is this something that has to be done separately? If separately, which
environmental variable and how?
Thanks,
Victor
Hi ,
It is not at all required to set / create any environment variables before
SQL Server installation. The
installation program will do all the necessary path entries automatically.
Thanks
Hari
MCDBA
"Victor" <vmgoldberg__NO-_*SPAM*_-PLEASE@.earthlink.net> wrote in message
news:ohMvc.36767$zO3.10088@.newsread2.news.atl.eart hlink.net...
> Does the setup for SQL also set the corresponding environmental variable,
or
> is this something that has to be done separately? If separately, which
> environmental variable and how?
> Thanks,
> Victor
>

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);
}
};

environment variable

hi,

can you show me how to get the value of an environment variable from a script task?
thanks!

Try Environment.GetEnvironmentVariable method
http://msdn2.microsoft.com/en-us/library/system.environment.getenvironmentvariable.aspx|||

Public Class ScriptMain
Public Sub Main()

Dim home As String = System.Environment.GetEnvironmentVariable("HOMEDRIVE") + System.Environment.GetEnvironmentVariable("HOMEPATH")

Dts.Variables("MyDocuments").Value = Path.Combine(home, "My Documents\")

Dts.TaskResult = Dts.Results.Success
End Sub

There is a sample package that illustrates the method in action here -

Environment Variables- Raw Files
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EnvironmentVariables-RawFiles.html)

Environment users and logins

Hi
We are currently migrating from MSSQL 2000 enterprice to MSSQL 2005
Enterprice, and with that doing a revision of our users.
We would like to be able to do a restore a backup of Production to test.
We would like user privileges on the servers to differ so a given user can
execute some stored procedures on one environment but not the other. Say, we
don’t want a service in test with the wrong connection string to be have
access to production.
We have previously done this by using local user groups on the servers, but
we loose the mapping between the Login name and the user name when we do the
restores.
Any ideas or best practices?
Best regards
MikaelMikael
> We would like to be able to do a restore a backup of Production to test.
No problem, just RESTORE command

> We would like user privileges on the servers to differ so a given user can
> execute some stored procedures on one environment but not the other. Say,
> we
> dont want a service in test with the wrong connection string to be have
> access to production.
MS has introduced in SS2005 lots of new features, like SCHEMA for example.
I'd suggets you to spend a hew days to study it

> We have previously done this by using local user groups on the servers,
> but
> we loose the mapping between the Login name and the user name when we do
> the
> restores.
http://blogs.msdn.com/lcris/archive.../03/567680.aspx
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:48261D3D-86AC-4B6F-8B0A-380101564866@.microsoft.com...
> Hi
> We are currently migrating from MSSQL 2000 enterprice to MSSQL 2005
> Enterprice, and with that doing a revision of our users.
> We would like to be able to do a restore a backup of Production to test.
> We would like user privileges on the servers to differ so a given user can
> execute some stored procedures on one environment but not the other. Say,
> we
> dont want a service in test with the wrong connection string to be have
> access to production.
> We have previously done this by using local user groups on the servers,
> but
> we loose the mapping between the Login name and the user name when we do
> the
> restores.
> Any ideas or best practices?
>
> --
> Best regards
> Mikael