setting SQL variable to NT environment variable

Question

Post reply

setting SQL variable to NT environment variable

pfrench3

SSC Enthusiast

Points: 190
More actions
February 14, 2003 at 9:01 am

#80289

Does anyone know how you would set a SQL variable to an NT environment variable.
For Example: NT environment variable 'Account' with the value of 'Microsoft' needs to be retrieved in TSQL.
Thanks in advance
Peter

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Use xp_cmdshell 'set' in T-SQL to output NT environment variables to a temp table and go from there.

pfrench3 SSC Enthusiast Points: 190 More actions · Answer 2

Thanks for the reply.

Could you give an example, I'm new to the TSQL side of things.

Much appreciated

Pete

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

create table #NTVars (Varable varchar(255))

insert into #NTVars EXEC master..xp_cmdshell 'set'

select * from #NTVars

drop table #NTVars

pfrench3 SSC Enthusiast Points: 190 More actions · Answer 4

Thanks again!

I don't actually want to set an NT variable I want to retrieve an NT variable from within a select statement. For example I would want to retrieve the value of the 'Host' NT environment variable in a sql statement.

I know you can do this with 'select @@servername' I would like to know how to do this with other NT variables.

Apologies for my lack of experience here.

Pete

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

That command is not used to set the variable. It retrieves the NT variables to a temp table so you can select the variables from it as you want.

tcartwright SSCertifiable Points: 5573 More actions · Answer 6

This was written along the same lines as Allens post. http://www.sqlservercentral.com/scripts/contributions/649.asp

Tim C.

//Will write code for food

One Windows to rule them all, One Windows to find them,

One Windows to bring them all and in the darkness bind them

In the Land of Microsoft where the Shadows lie.

Tim C //Will code for food

Antares686 SSC Guru Points: 125444 More actions · Answer 7

The problem I always found with doing it using set was the fact it includes the variable name.

If you say do

SET WINDIR

you get

windir=C:\WINDOWS

I instead suggest use echo if you are going after a specific environment variable do like so.

SET NOCOUNT ON

CREATE TABLE #env (envir varchar(2000))

DECLARE @env as VARCHAR(2000)

DECLARE @getstr as VARCHAR(2100)

SET @env = 'windir'

SET @getstr = 'echo %' + @env + '%'

INSERT #env EXEC xp_cmdshell @getstr

SELECT @env = envir FROM #env WHERE envir IS NOT NULL

DROP TABLE #env

PRINT @env

tcartwright SSCertifiable Points: 5573 More actions · Answer 8

Excellent suggestion antares, as echo would alos not allow them to change it either. BTW my script returns the data from the set command as two columns by splitting the returned data on the '='.

Tim C.

//Will write code for food

One Windows to rule them all, One Windows to find them,

One Windows to bring them all and in the darkness bind them

In the Land of Microsoft where the Shadows lie.

Tim C //Will code for food

tcartwright SSCertifiable Points: 5573 More actions · Answer 9

Hmmmm, I think I will modify my script to error if passing in an = to keep users from setting environ variables. Keep the users from accidentally / maliciously overwriting an environ variable. Even though in Win 2000 the normal set command only allows users to set user environ variables, it could be potentially harmful.

Tim C.

//Will write code for food

One Windows to rule them all, One Windows to find them,

One Windows to bring them all and in the darkness bind them

In the Land of Microsoft where the Shadows lie.

Tim C //Will code for food