?
Solved

Retrieve environmental variable (%temp%)

Posted on 2003-03-13
10
Medium Priority
?
468 Views
Last Modified: 2008-03-17
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.
0
Comment
Question by:adwiseman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 8131769
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
0
 
LVL 14

Author Comment

by:adwiseman
ID: 8131804
But how would you return that to a variable in a procedure?

That's the question
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 300 total points
ID: 8131869
create table #test(Path varchar(8000))
set rowcount 1
insert #test(Path)
exec master.dbo.xp_cmdshell 'set temp'
set rowcount 0
select Temp=substring(Path,charindex('=',Path)+1,len(Path)) from #test
drop table #test
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 3

Expert Comment

by:Satyabodhi
ID: 8132811
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.

0
 
LVL 14

Author Comment

by:adwiseman
ID: 8135979
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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 8136640
Good point Satyabodhi--that is the %temp% variable of the SQL Server--not the client....

Brett
0
 
LVL 3

Expert Comment

by:Satyabodhi
ID: 8138313
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.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8138448
You can combine solutions.

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

Author Comment

by:adwiseman
ID: 8138560
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.
0
 
LVL 14

Author Comment

by:adwiseman
ID: 8138658
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.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question