February 17, 2010 at 9:44 am
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
____________________________________________________
February 17, 2010 at 9:54 am
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
February 17, 2010 at 10:07 am
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
____________________________________________________
February 17, 2010 at 10:37 am
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
____________________________________________________
February 17, 2010 at 11:16 am
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
February 17, 2010 at 11:34 am
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
____________________________________________________
February 17, 2010 at 11:43 am
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
February 18, 2010 at 1:52 am
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
____________________________________________________
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply