UNC Path of filename

Question

Post reply

UNC Path of filename

C# Gnu

SSCertifiable

Points: 5973
More actions
February 17, 2010 at 9:44 am

#216834

How could I get the UNC path for a dirve letter filename such as
local
C:\templates\Template1.doc
or network
X:\templates\Template1.doc
C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

not sure how much help this really is;

but i found it in two places

by running "NET USE" in a command window, i get results that i could use with xp_cmdshell:

Status Local Remote Network

-------------------------------------------------------------------------------

OK Z: \\web1\Lowell Microsoft Windows Network

\\TFS\Sites\HDS Web Client Network

The command completed successfully.

i was also able to find any mapped share in the registry; but it is mapped to my SID and not a general LOCAL_MACHINE entry or anyting.

HKEY_USERS\S-1-5-21-1645522239-436374069-1708537768-1251\Network

from there, there was a "Z" folder, which internally contained the mapping and a bunch of other stuff

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

C# Gnu SSCertifiable Points: 5973 More actions · Answer 2

Thanks Lowell

I have struggled with it - thinking I am daft and should be simple...

I found WNetGetUniversalName using this C#

http://bytes.com/topic/c-sharp/answers/248531-logical-drives

But thats means I will have to wrap/deploy in CLR - unless there is an easier way ....

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu SSCertifiable Points: 5973 More actions · Answer 3

Am out my depth here : I made this up - it returns NULL!

ALTER FUNCTION [dbo].[fn_DriveToUNC] (@Drive VARCHAR(3))

RETURNS varchar(1000)

-- RBriggs 10/07/2009

-- Get UNC path of drive letter

AS

BEGIN

declare@objWNet int

declare @UNC varchar(1000)

exec sp_OACreate 'WNetmpr', @objWNet out

exec sp_OAMethod @objWNet, 'WNetGetUniversalName', @UNC out, @drive

exec sp_OADestroy @objWNet

RETURN @UNC

END

go

SELECT dbo.fn_DriveToUNC('C:')

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Lowell SSC Guru Points: 323504 More actions · Answer 4

from what i just read WNetGetUniversalName 2will give you the UNC path to a shared resorce, but not a local resource;

i think you'd have to build that yourself, and could only assume default shares, so a local drive would be something like this:

declare @path varchar(255)

set @path = 'C:\Program Files\CScrew'

select '\\' + convert(varchar(255),serverproperty('machinename')) + REPLACE(@path,':\','$\')

now how do you know the F:\ drive is local or a network resource...maybe if the code you had did not return anything from the sp_oacreat, you'd assume a local drive??

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

C# Gnu SSCertifiable Points: 5973 More actions · Answer 5

Thanks Lowell

1/I think I am off track with the up_OACreate, I think the DLL is MPR.DLL

But can't find reference to it being used with sp_OACreate.

I just kind of hoped I would be lucky, the @objWNet is null straight after the sp_OACreate call.

I also Tried

exec sp_OACreate 'mpr.WNetGetUniversalName', @objWNet out

but again @objWNet is null, so not much progress there!

2/ I have realised doing this test in my scenario might not make sense as it is a user that is specifying the path.

If the user specifies 'c:\FolderName' for a search path I won't know on server if they meant their local machine 'c:\' or the server 'c:\'. So I am thinking to prevent the user from specfying a path with a drive letter, and ask for a UNC path instead....

Any determination of UNC path from drive letter I think I have realised would have to be done client side.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Lowell SSC Guru Points: 323504 More actions · Answer 6

ok i see what you are trying to do;

in order for this to work, the acount that is running the services will need domain admin priviledges in order to browse to the default shares like \\machinename\c$

but it's still a variation of what i pisted, except using host_name(0 instead of the serverproperty:

declare @path varchar(255)

set @path = 'C:\Program Files\CScrew'

select '\\' + convert(varchar(255),host_name()) + '\' + REPLACE(@path,':\','$\')

the host_name() would resolve to whatever machine is connecting...hopefully from within the smae network/domain the server is on.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

C# Gnu SSCertifiable Points: 5973 More actions · Answer 7

Thanks Lowell

I need a user to be able to specify local and network drives, so I opted to choke if drive letter specfied, at least for now, but when there is a proper front end I could build in a drive / folder select and get the correct UNC path from that using the WNetGetUniversalName. I think in my circumstance am not sure using hostname() I won't know if 'c:' means users local drive or servers drive.

So for now I have :

IF LTRIM(@path) LIKE '[a-z]:%'

BEGIN

RAISERROR ('Please specify UNC path, rather than a drive letter.

For example if you wish to search network path z:\FolderName

then specify "\\{ServerName}\FolderName.

If you wish to specify a local drive eg c:\FolderName

then specify \\{YourMachineName}\FolderName"',

16, 1, N'abcde');

RETURN

END

I might build quick front end in Excel VBA - it would lend itself nicely as I can drop results into a new tab on each run - and won't need install of .net runtime 😉

Many thanks

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015