SQL Server Temp Dir?

Question

exec master..xp_cmdshell 'set'

I need to get a temporary OS directory without resorting to using xp_cmdshell. I am using MSSQL 2008. How can I do this?

Well, there seems to be no way to do this from TSQL. I believe that SQL Server knows about% temp% because it should use it, but well.

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

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=','' )

Thank.

Answer 1

To read environment variables %TEMP%either %TMP%you need to use xp_cmdshell or some CLRs (not sure about the resolution). This gives you a folder with the service account. "General" -%WINIR%\Temp

This is not what you usually do in everyday SQL

Answer 2

, tempdb, ,

execute tempdb.dbo.sp_helpfile

()

... , , ? sp_OA, , .

Answer 3

Try running SSMS as an administrator.

Check this link ....

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

Answer 4

You can use an Scripting.FileSystemOLE object to get a temporary Windows 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