Determine full My Documents path for current user

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Determine full My Documents path for current user

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-14 : 04:40:30

Anyone know what transact sql can be used to determine full My Documents path for current user. Resolve ("%USERPROFILE%\My Documents") to C:\Users\sharlington\My Documents for example.

I used this with xp_cmdshell and it returns C:\Users\default\My Documents;

Set @String1 = 'for %i in ("%USERPROFILE%\My Documents\") do @echo. %~$PATH:i'
Set @sqluu = 'EXEC master..xp_cmdshell '''+@String1+''''
Exec (@sqluu)

This is strange because if I run this from a cmd prompt it returns C:\Users\sharlington\My Documents

for %i in ("%USERPROFILE%\My Documents\") do @echo. %~$PATH:i

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-14 : 04:59:02

Check which account xp_cmdshell is running under.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-14 : 05:03:17

And how would I do that?

Thanks

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-14 : 05:17:21

If you use something like select suser_sname() it returns XTRALISCORP\sharlington. Not quite what I was after?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-14 : 05:31:02

Try master..xp_cmdshell 'echo %USERNAME%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-16 : 18:51:51

master..xp_cmdshell 'echo %USERNAME%' returns 'system' and not 'C:\Users\sharlington\My Documents'. master..xp_cmdshell 'echo %USERPROFILE%' returns 'C:\Users\default which was my orginal question because for %i in ("%USERPROFILE%\My Documents\") do @echo. %~$PATH:i under a cmd prompt returns the correct output C:\Users\sharlington.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-17 : 01:31:42

Any ideas how to do this without xp_cmdshell or clr? Actually any ideas with would also be appreciated :)

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-18 : 02:41:16

Can anyone see a problem with this. It seems to work but I have not tested it in many cases yet. Result is;

Path
C:\Users\sharlington\My Documents

Use [vc]

Declare @String1 nvarchar (255)
Declare @sql nvarchar (255)
Declare @UserName nvarchar (255)
Declare @UserName2 nvarchar (255)
Declare @LEN int
Declare @CHAR int
Declare @testlen nvarchar (255)

--This failed on one of my Vista PC's because the name returned from suser_sname() (steveha) is different to what is returned from echo %USERPROFILE% (steveha.adpro.com.au) in a cmd prompt.
--Also Vista seems to be called 'Documents' while XP and Win 7 are called 'My Docuemnts'.
--It worked on an XP and the four Win 7 PC's I tested on (three on a domain and one on a workgroup).

Create Table #t (loginname nvarchar (255))
Insert into #t
Select suser_sname()
--Select * From #t
SET @LEN = (Select LEN(RIGHT(loginname, CHARINDEX('\', REVERSE(loginname)) - 1)) AS loginname from #t)
Print @LEN
SET @CHAR = (Select (CHARINDEX('\', loginname)+1) from #t)
Print @CHAR
SET @UserName = (SELECT SUBSTRING(loginname, @CHAR, @LEN) UserName from #t)
Print @UserName
Set @String1 = 'for %i in ("%USERPROFILE%\My Documents\") do @echo. %~$PATH:i'
Set @sql = 'EXEC master..xp_cmdshell '''+@String1+''''
Create Table #p (path nvarchar (255))
Insert into #p
Exec (@sql)
--Select * From #p

--Length check required for XP (XP = Default User, Vista/Win 7 = Default)
SET @testlen = (Select Top (1) * From #p)
IF @testlen Like '%Default User%'

BEGIN
SET @LEN = (Select '12')
Print @LEN
END
ELSE
SET @LEN = (Select '7')
Print @LEN

SET @CHAR = (Select (CHARINDEX('default', path)) from #p Where path like '%default%')
Print @CHAR
SET @UserName2 = (SELECT SUBSTRING(path, @CHAR, @LEN) UserName from #p Where path like '%default%')
Print @UserName2
Select LTRIM(REPLACE(path, @UserName2, @UserName)) As Path From #p where path like '%default%'
Drop Table #t
Drop Table #p

Sachin.Nand

2937 Posts

Posted - 2011-10-18 : 06:39:00

Cant you use a CLR function ?.Net has rich set of functions related to Enviorment variables.

PBUH

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-18 : 06:41:22

I thought so as well but I could not get it working. see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=166813.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-18 : 23:28:09

I also could not get this to work either.

DECLARE @Registry_Value VARCHAR(1000)

EXECUTE master.dbo.xp_instance_regread 'HKEY_CURRENT_USER', 'Software\\Microsoft\\Windows\\CurrentVersion\\Explorer\\Shell Folders', 'Personal', @Registry_Value OUTPUT

SELECT @Registry_Value

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-19 : 00:08:20

HKEY_CURRENT_USER will be for the SQL Server service account as that's the context of SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources