SQL Server Temp Dir?

Question

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.

Answer 1

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