SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UNC Path of filename


UNC Path of filename

Author
Message
C# Gnu
C# Gnu
SSChasing Mays
SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)

Group: General Forum Members
Points: 657 Visits: 659
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

Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37297 Visits: 40285
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
C# Gnu
SSChasing Mays
SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)

Group: General Forum Members
Points: 657 Visits: 659
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
C# Gnu
SSChasing Mays
SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)

Group: General Forum Members
Points: 657 Visits: 659
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
Lowell
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37297 Visits: 40285
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
C# Gnu
SSChasing Mays
SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)

Group: General Forum Members
Points: 657 Visits: 659
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
Lowell
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37297 Visits: 40285
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
C# Gnu
SSChasing Mays
SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)

Group: General Forum Members
Points: 657 Visits: 659
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 Wink

Many thanks

C# Gnu
____________________________________________________


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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search