Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UNC Path of filename Expand / Collapse
Author
Message
Posted Wednesday, February 17, 2010 9:44 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #867217
Posted Wednesday, February 17, 2010 9:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 12,905, Visits: 32,160
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #867225
Posted Wednesday, February 17, 2010 10:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #867243
Posted Wednesday, February 17, 2010 10:37 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #867289
Posted Wednesday, February 17, 2010 11:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 12,905, Visits: 32,160
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #867327
Posted Wednesday, February 17, 2010 11:34 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #867338
Posted Wednesday, February 17, 2010 11:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 12,905, Visits: 32,160
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #867347
Posted Thursday, February 18, 2010 1:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #867808
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse