Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Retrieve environmental variable (%temp%)

Posted on 2003-03-13
10
Medium Priority
?
471 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

569 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