Hello,

I am trying to change the value of an environment (MSSQL service user)
variable with the following tsql:

exec master..xp_cmdshell 'SET test=test'

unfortunately the variable is not changed; anyone would know why this
doesn't work?

how can I achieve this?

thanks and regards,

Olivier.

An enviornment variable set with the SET command is temporary to the command
prompt session. A new execution of xp_cmdshell invokes a new session. If you
want the environment variable setting to be permanent, you need to edit the
registry or use COntrol Panel || System:Advanced:Environment Variables.

Windows Resource kit does have command-line utility called SETX.EXE, which
you can invoke from xp_cmdshell.

Linchi

"OlivieCora" wrote:

> Hello,
>
> I am trying to change the value of an environment (MSSQL service user)
> variable with the following tsql:
>
> exec master..xp_cmdshell 'SET test=test'
>
> unfortunately the variable is not changed; anyone would know why this
> doesn't work?
>
> how can I achieve this?
>
> thanks and regards,
>
> Olivier.

"OlivieCora" <OlivieCora@discussions.microsoft.com> wrote in message
news:5FEA7CBE-5038-40C9-A73A-B6F1CE49E115@microsoft.com...
> Hello,
>
> I am trying to change the value of an environment (MSSQL service user)
> variable with the following tsql:
>
> exec master..xp_cmdshell 'SET test=test'
>
> unfortunately the variable is not changed; anyone would know why this
> doesn't work?
>
> how can I achieve this?
>
> thanks and regards,
>
> Olivier.

Hi

The environment variable will be set for the extent of invocation of
xp_cmdshell e.g

if you create a file C:\temp\fred.bat containing
@@echo off
SET test=ttt
echo %test%

From

EXEC xp_cmdshell 'C:\temp\fred.bat'

You would get
output
-------
ttt
NULL

Change fred.bat to
@@echo off
echo %test%

output
-------
ECHO is off.
NULL

i.e the original SET is no longer valid.

If you want to have something picked up in other xp_cmdshells you could
either change the registry or use a file and materialise the values in that,
The third alternative is to specify them in the command itself each time e.g
write a batch script that takes parameters.

John

Hi Linchi Shea,
that's what I figured out... SETX is exactly what I was looking for

thanks a lot!

Olivier.

"Linchi Shea" wrote:

> An enviornment variable set with the SET command is temporary to the command
> prompt session. A new execution of xp_cmdshell invokes a new session. If you
> want the environment variable setting to be permanent, you need to edit the
> registry or use COntrol Panel || System:Advanced:Environment Variables.
>
> Windows Resource kit does have command-line utility called SETX.EXE, which
> you can invoke from xp_cmdshell.
>
> Linchi
>
> "OlivieCora" wrote:
>
> > Hello,
> >
> > I am trying to change the value of an environment (MSSQL service user)
> > variable with the following tsql:
> >
> > exec master..xp_cmdshell 'SET test=test'
> >
> > unfortunately the variable is not changed; anyone would know why this
> > doesn't work?
> >
> > how can I achieve this?
> >
> > thanks and regards,
> >
> > Olivier.