Hi everyone.

I have done quite a bit of research on this, but could not find a good
answer. I need to get the UNC path from a mapped drive letter where the
drive is mapped to another machine (not the SQL Server). The drive is
mapped the same on the server and all client stations. My main course was
to use sp_OACreate to create a filesystemobject and use the ShareName
property of the Drive object. The problem with that is that this only works
for the server's local C drive. When I try to use any mapped drive, it
fails. Here's what I've tried:

Declare
@src varchar(255), @desc varchar(255), @fso int, @Ret int, @DriveToken int,
@UNC nvarchar(100)

Exec @Ret = sp_OACreate 'Scripting.FileSystemObject', @fso out
Print @Ret
Exec @Ret = sp_OAMethod @fso, 'GetDrive', @DriveToken out, 'F'
Print @Ret
IF @Ret <> 0 Begin
Exec sp_OAGetErrorInfo @DriveToken, @src out, @desc out
Select hr=convert(varbinary(4),@Ret), Source=@src, Description=@desc
End

And the result of the error is:

0x800A0044 NULL NULL

Again, this works fine for C, but not for mapped drives.

I also found this:

Declare @Drive varchar(3), @Ret int
Set @Drive = 'C:\'

declare @objWNet int
declare @UNC varchar(1000)
exec sp_OACreate 'mpr.WNetGetUniversalName', @objWNet out
exec sp_OAMethod @objWNet, 'WNetGetUniversalName', @UNC out, @Drive
exec sp_OADestroy @objWNet

Print @UNC

But this fails on the first create.

Does anyone know a way to get the UNC path from a mapped drive?

Thanks.

Matthew Wells
matthew.wells (AT) firstbyte (DOT) net

Matthew Wells (matthew.wells (AT) firstbyte (DOT) net) writes:
Supposedly only the C drive is mapped as far as SQL Server is concerned.
Disk mapping is a user-level thing, and they rarely apply for services.
So I'm afraid that you are in a dead end.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Actually in this case the server has the same mapped drives as all the users
so my question is still applicable.


"Erland Sommarskog" wrote in message
news:Xns9EA7CCAECDEB6Yazorman (AT) 127 (DOT) 0.0.1...

Matthew Wells (matthew.wells (AT) firstbyte (DOT) net) writes:
Supposedly only the C drive is mapped as far as SQL Server is concerned.
Disk mapping is a user-level thing, and they rarely apply for services.
So I'm afraid that you are in a dead end.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Matthew Wells (matthew.wells (AT) firstbyte (DOT) net) writes:
No, the server does not have any mapped drives. The user logged in on the
console may have. SQL Server is not logged in the console, but is logged on
as a service.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Erland is correct. Sql server is logged in (probably) as a local account, unless you've changed it to a service account.

You could have a proc run at startup to create a mapped drive:

USE master;
GO
-- first set the server to show advanced options
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
-- then set the scan for startup procs to 1
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE

IF OBJECT_ID('usp_CreateDrive') IS NOT NULL
DROP PROC usp_CreateDrive
GO
-- crate a test stored procedure
CREATE PROC usp_CreateDrive
AS

EXEC xp_cmdshell 'net use x: \\Server\ShareName'

GO
-- set it to run at sql server start-up
exec sp_procoption N'usp_CreateDrive', 'startup', 'on'


I suppose you could try running your code in the startup proc as well, but I imagine that may get a little henky, as you would have to login to the account that sql is running under, create the mapped drives and ensure that it has reconnect at login enabled, but I'm not even sure that would work, astechnically it's not really 'logged in' when the service is running I think. I'd just use the net use to map the drive on startup.

HTH (code untested, but should work)
Bender

Reply test

"Matthew Wells" wrote in message news:eR8fp.20449$hP6.5091 (AT) newsfe19 (DOT) iad...

Actually in this case the server has the same mapped drives as all the users
so my question is still applicable.


"Erland Sommarskog" wrote in message
news:Xns9EA7CCAECDEB6Yazorman (AT) 127 (DOT) 0.0.1...

Matthew Wells (matthew.wells (AT) firstbyte (DOT) net) writes:
Supposedly only the C drive is mapped as far as SQL Server is concerned.
Disk mapping is a user-level thing, and they rarely apply for services.
So I'm afraid that you are in a dead end.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Matthew Wells (matthew.wells (AT) firstbyte (DOT) net) writes:
I don't see your machine, but disk mappings are set up when you login,
or because you run NET USE manually. I still don't think the service account
has any disks mapped.

What happens if you try:

exec xp_cmdshell 'DIR F:\'

assuming that you think F is a mapped disk?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Hi Erland,

You seem to have missed the first line of the post, "Reply test"

Best,
Henk

On 02-05-2011 23:43, Erland Sommarskog wrote: