Post reply

Use sp_OACreate to connect to share drive ..

  • March 16, 2011 at 10:15 am

    #236414

    Does anybody knows where I can find doc for this ?

    I would like to do something like (in vbs)

    Set objNetwork = CreateObject("WScript.Network")

    objNetwork.MapNetworkDrive "K:", "\\ALAN\home"

    with sp_OaCreate ....

  • March 16, 2011 at 10:54 am

    #1299499

    I would recommend you leave the sp_OA procs disabled on your instance and start using the SQLCLR for tasks like this...

    If you're being boxed into using the OLE Automation procs here is a decent starting point with code samples:

    http://msdn.microsoft.com/en-us/library/ms191151.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 16, 2011 at 12:06 pm

    #1299557

    tks.

    1. So what would be the code to map a network share?

    2. What would be the code to retrieve folder contents?

    Tks again.

  • March 16, 2011 at 12:20 pm

    #1299571

    I must agree, with the availability of SQLCLR the use of the OLE automation should basically never happen.. In other words don't enable them. Also as a practice I also keep xp_cmdshell disabled.

    Very rarely do you need to map a drive, you can usually refer to any location that is on another ser by UNC path such as \\servername\sharename\path\path2\filename.txt

    It is also considered a bad practice to use mapped drives in SQL because you can't really trust their existence. Whereas a UNC is a fully qualified path to the file. The biggest issue is permissions on the share and file, which you would likely have anyway..

    CEWII

  • March 16, 2011 at 12:30 pm

    #1299578

    DZN (3/16/2011)

    2. What would be the code to retrieve folder contents?

    Time to hit the books...getting the contents of a folder is a trivial task in C# but it's a learning question too fundamental to explain in a forum setting. There are lots of good online resources for learning .NET...or even better, support your local bookstores.

    Elliott Whitlow (3/16/2011)

    Also as a practice I also keep xp_cmdshell disabled.

    Amen!

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 16, 2011 at 12:48 pm

    #1299592

    Yes I agree with you. Issue was not really to map drive (which is by the way not the purpose) but rather to connect to a shared resources with user ID & pwd ....

    And to be able to do it I do not know the good solution (except doing it trough SQLCLR...?)

  • March 16, 2011 at 12:52 pm

    #1299598

    i have this saved in my snippets on how to map a drive from xp_cmdshell:

    exec master.dbo.xp_cmshell 'NET USE G: \\UNCPath\d$ /user:domain\user password'

    GO

    RESTORE Database DBName FROM Disk = 'G:\PAth to bak'

    GO

    EXEC master.dbo.xp_cmdshell 'NET USE G: /DELETE'

    --NET USE path password /USER:domainname\username

    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!

  • March 16, 2011 at 12:59 pm

    #1299602

    tks. this at least ill enable me to start....

  • March 16, 2011 at 1:04 pm

    #1299604

    Lowell's method does work.. It violates my no xp_cmdshell rule..

    I really think that if you must impersonate a different user then you might look at calling it from a job using proxies.. Or SQLCLR.

    CEWII

  • March 16, 2011 at 1:18 pm

    #1299613

    I have tried with proxies but without success....

    And SQLCLR will probably the next step after pilot... But right now I have no time to do it (and I'm not sure SQLCLR is available witj .net on the server....

    So if you have an example with proxies, it's more than welcome ...

  • March 17, 2011 at 12:16 am

    #1299793

    I don't have anything with proxies but I have a fair amount of SQLCLR code for files. .Net framework 2 is all that is required..

    CEWII

Viewing 11 posts - 1 through 10 (of 10 total)

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