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
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