CODEWITHSUNDEEP
sql-servertemp
Snowy
Snowy

Reputation: 6132

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.

Upvotes: 3

Views: 20501

Answers (4)

podiluska
podiluska

Reputation: 51514

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

Upvotes: 0

Ben
Ben

Reputation: 9

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

Upvotes: 0

gbn
gbn

Reputation: 432672

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

Upvotes: 2

Philip Kelley
Philip Kelley

Reputation: 40359

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.

Upvotes: 1

Related Questions