asked on

Retrieve environmental variable (%temp%)

I want to get a environmental variable returned to a procedure in a string. the procedure or function that performs this must be able to perform this for multiple users at the same time.

ex environmental variable %temp% would return "c:\winnt\temp"

1 restriction, no DTS packages can be used.

arbert

You can use this:

xp_cmdshell 'set temp'

That will return the setting for temp in a recordset.

However, whoever runs xp_cmdshell has to have sysadmin rights OR you have to enable the proxy account to execute xp_cmdshell (not recommended).

Brett

adwiseman

ASKER

But how would you return that to a variable in a procedure?

That's the question

ASKER CERTIFIED SOLUTION

ispaleny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Satyabodhi

Your question is puzzling because you say "perform this for multiple users at the same time". The environment value when you run xp_cmdshell will ALWAYS be that for the user that runs SQL Server, not the client's environment.
To see all of the environment values for that user, try :
exec master.dbo.xp_cmdshell 'set'

If you use the Registry (where environment values live)
you have the same problem. It is the Registry of the server, not of the client.
If you have multiple users, you likely have them running from multiple client machines. The Server can't tell you about the environment on the client machine.

adwiseman

ASKER

ispaleny, you've got it. Thanks! I didn't know you could do that. It would work better if I did this though,

exec master.dbo.xp_cmdshell 'echo %temp%'

Satyabodhi,
what I ment by "perform this for multiple users at the same time" was simple that 2 users wound not conflict with each other while using the answer given above. Which would occure if a global temporary table or a regular table was used.

arbert

Good point Satyabodhi--that is the %temp% variable of the SQL Server--not the client....

Brett

Satyabodhi

I think there would be less conflict if a regular table were to be used to hold the directory path. I do that in my system. Share locks on read-only access are cheap. xp_cmdshell is relatively expensive in execution. Allowing xp_cmdshell is also a well-known security risk.
If you are saying you don't want conflict on the directory access, as opposed to the table access, then the xp_cmdshell solution does nothing good for you, because it returns the same value for every user.

ispaleny

You can combine solutions.

1. Create SP getting %temp% into table XXX. Mark it "run on startup"
2. Users will query table XXX.

adwiseman

ASKER

That is a good idea ispaleny. I would have to schedule a DTS to maintaine this table so it stays up to date if a variable should change. I don't like having to store something twice on the server when it is accessable by other means.

I do not wish to look at the clients system, this is a dir on the SQL server.

adwiseman

ASKER