How to get windir, %temp%, etc

[jenlion]

How do I get %windir% or %temp%?

I am doing a bcp to a file. %temp% works fine for that. But then, when I do this:

EXEC master.dbo.xp_sendmail
    @recipients = @emailaddr,
    @message = @message,
    @Subject = N'Your order has shipped',
    @attachments = @FileName

I get this:

Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80004005

If I set @FileName to c:\something, works great. Set it to %temp%\something, no good. File is created fine; email is what fails. I guess it doesn't like the relative name.

I see I can get the Program Files directory by reading the registry, but I can't find windir or temp dir in the registry (maybe not doing the right search?).

Can someone tell me how to get the actual location? I'd like to do something like this, if you know what I mean:
declare @tempdir varchar(100)
set @tempdir = getdir(%temp%)
set @FileName = @TempDir\something

 

[sunila7]

%windir% and %temp% are environment variables. This can be found in HKEY_LOCAL_MACHINE \SYSTEM \CurrentControlSet \Control \Session Manager \Environment location in registry..

Hope this helps.

Sunil

 

[jenlion]

Hi Sunil,

I did find that, but it says that windir is actually %systemroot% and temp is %systemroot%\temp. So, I have the same problem.

SOMEHOW the computer knows that %systemroot% is c:\windows, because I can cd %systemroot% and go there.

(I'm using server 2003, btw).

Thanks anyway, does anyone else have a suggestion??

 

[jenlion]

OK, I have a way to do this now.

From

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1056858,00.html:

Create Procedure dbo.sp_GetEnvVarValue
        (@EnvVarName sysname,
         @ResultValue sysname OUTPUT) 
AS
BEGIN

    set noCount on 

    -- Create a temporary table to store environment variables list
    Create table #EnvirnmentVariables (output varchar(1000))  

    -- populate the list
    Insert #EnvirnmentVariables exec master..xp_cmdshell 'set'

    -- select from the list the part after the = sign 
    -- where Environment key parameter equals the part before the = sign 
    Select @ResultValue = Substring (output,
                      CharIndex ('=',output) +1,
                      len(output) - CharIndex ('=',output))
    from #EnvirnmentVariables
    where 
     upper (@EnvVarName) = upper (Substring (output,1,CharIndex ('=',output)-1))
    Drop table  #EnvirnmentVariables 
    set noCount off 
END
GO

Note: I used a procedure rather than a user-defined function because temporary tables cannot by created and accessed in a UDF.

Here is an example of a call to the function:

 
-- Invoke the procedure
USE pubs
GO
declare @outval sysname 
exec master.dbo.sp_GetEnvVarValue 'OS',@outval OUTPUT
print @outval

This gives us the operating system name (Windows_NT in my case).

Hope it's OK to reproduce here -- I just don't know if the link will stay good forever, and I found this idea pretty useful.

I used

  exec sp_GetEnvVarValue 'Temp',@outval OUTPUT

to get the temp location. Works great1!