SQL Server Temp Dir?

exec master..xp_cmdshell 'set'

I need to get the OS temporary directory without resorting to using xp_cmdshell. I am using MSSQL 2008. What's the best way I can do that?

Well, it looks like there is no way to do that from TSQL. I believe SQL Server knows about %temp% because it must use it, but oh well.

Well can anyone recommend a way to make this code more compact/tighter?

Set NoCount On
Declare @t VarChar(256)
Declare @env Table ( [Parts] VarChar(256) )
Insert Into @env 
Exec Master..Xp_CmdShell 'set' 
Set @t = (  Select Top 1 [Parts] From @env Where [Parts] Like 'temp=%'  )
Select Replace(@t , 'temp=','' )

Thanks.

You have to use xp_cmdshell or some CLR (not sure of permissions) to read the environment variables %TEMP% or %TMP%. This gives you the service account folder though. The "common" one is %WINIR%\Temp

This isn't something that you'd normally do about in day to day SQL

The location of the file used by the tempdb database can be quickly found by running

execute tempdb.dbo.sp_helpfile

(edit)

...that's not what you're looking for, is it? Barring diving into the sp_OA procedures, I don't think there is any simple way to access OS information of this nature.

Try running SSMS as Administrator.

Check this link out....

http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

You can use the Scripting.FileSystem OLE object to get the windows temp folder.

declare 
    @tempFolder varchar(260),
    @oleResult int,
    @fs int,
    @folder int;    

    exec @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @fs output;
    exec @OLEResult = sp_OAMethod @fs, 'GetSpecialFolder', @folder output, 2        
    exec sp_OAGetProperty @folder , 'Path', @tempfolder OUT
    exec @oleResult = sp_OADestroy @folder
    exec @oleResult = sp_OADestroy @FS

    select @tempFolder