locked
To access a shared folder providing user name and password RRS feed

  • Question

  • Hi,

     

    My code should copy files from a shared folder.

    the share can be accessed by all who can provide a specified username and password.

     

    I use the following code:

    But where to specify username name password to access that folder?

     

    DECLARE @cmdstr varchar(1000)

    set @cmdstr = 'copy \\servername\foldername$\  C:\DataExTest'

    print @cmdstr

    EXEC xp_cmdshell @cmdstr

     

    Any sort of help would be highly appreciated.

     

    Thanks in advance

     

     

     

    Sunday, February 17, 2008 10:03 AM

All replies

  • I am not sure if the COPY command can handle that. One option that I can suggest is the following.

    1. map the network folder using dos command 'NET USE' (by running XP cmdshell) - you need to provide username and passwrod here.
    2. copy the file
    3. disconnected the mapped drive using "NET USE /DELETE"
    Sunday, February 17, 2008 10:36 AM
  • Hi,

     

     

    You need to map the shared drive if you need to provide username and password before copying data. Try the below query.


    Code Snippet

    Exec master..Xp_cmdshell 'net use Z: "\\servername\foldername$\" /USER:Uername Password' -- To map the drive

    DECLARE @cmdstr varchar(1000)
    set @cmdstr = 'copy Z:\  C:\DataExTest'
    print @cmdstr
    EXEC xp_cmdshell @cmdstr

    Exec master..Xp_cmdshell 'net use /d Z:'   --- To disconnect the mapped drive

     

     

    You can proceed with your query if you provide necessary privilege on the share for SQLservice account. So that mapping with different username and password isn't necessary. FYI.

    • Proposed as answer by RePtiR Monday, May 14, 2018 6:38 PM
    Sunday, February 17, 2008 10:38 AM
  • Thank You  so much Vidhya and Jacob.....

    I am trying to incorporate the code into a stored proc and finally to a job.

     

    So If the file to be copied is not in the source(shared folder), i dont have to proceed, in need to quit the job with failure.

    how to handle it in a production environment?

    And how would  someone who manages the job know that the file is not in the source, and that is the cause for the job to fail?

     

    Regards

    Sunday, February 17, 2008 8:11 PM
  • If you are importing only one file from the shared drive with static filename, you can use the below query this will help you.

    Confirm whether you are copying only one file or more files from the shared drive.
    ---------------------------------------------------------------------------------------------------------------
    Exec master..Xp_cmdshell 'net use Z: "\\servername\foldername$\" /USER:Uername Password' -- To map the drive DECLARE @cmdstr varchar(1000) Declare @result tinyint set @cmdstr = 'copy Z:\filename.txt C:\DataExTest' Exec master..xp_fileexist 'Z:\filename.txt', @result output IF (@result = 1) --if file exists import it, otherwise exit begin EXEC master..xp_cmdshell @cmdstr else print 'No files exists in the source directory and hence the process has been terminated' -----------To notify the person about job failure---------- Exec msdb.dbo.sp_send_dbmail @profile_name='Your Mail Profilename',@recipients='Mail IDs to send the mail', @subject='Your Job name failed', @body='Job failed since source file is not present, Pls check' Exec master..Xp_cmdshell 'net use /d Z:' --- To disconnect the mapped drive
    ---------------------------------------------------------------------------------------------------------------

    You need to change mail profilenam, job name in subject and recipients mail ids before using the script.
    Monday, February 18, 2008 2:49 AM
  • Probably you can use the dos command IF EXIST and find if the file exists: http://www.allenware.com/icsw/icswidx.htm

    When your process fails, probably you should write the error details to a log file or to a table so that some one can read it later to understand if the job failed or not.

     

    Since you need to run lots of DOS commands, probably it is a good idea to create a batch file (that runs all the tasks) and run it from xp_cmdshell.

     

    Monday, February 18, 2008 2:50 AM
  •  

    Vidhya....

    Thanks again for answering.

    I need to copy atleast 2 files.

    However i can proceed further if i copy one file.

    The file name would be different each time. It would start with for ex: abc_def and have datestamp YYYYMMDD attached to it.ie: abc_def20080217.xls.

     

    so when i try  master..xp_fileexist 'Z:\filename*.xls'

    No matter the file exists or not it always returns 0.

     

    Is there an alternate way for this case?

     

     

    Monday, February 18, 2008 6:11 AM
  • You can try the below piece of code, which will generate the filename format like abc_def20080217.xls. 

    Declare @fname varchar(30)
    set @fname='Z:\abc_def'+convert(varchar(10),getdate(),112)+'.xls'
    Exec master..xp_fileexist @fname
    Monday, February 18, 2008 9:58 AM
  • Thanks again Vidhya for anwering.

     

    The above command always gives me result as 0.

     

    The XP_fileexist works only if i specify the correct complete file name along with the the command.

    Is there an alternative?

     

    Thanks & Regards,

     

    Wednesday, February 20, 2008 12:26 AM
  • Hi,

    Will the files in the source will be deleted once file copy completed?? So that daily you will receive the new files??

     

    Wednesday, February 20, 2008 12:48 AM
  • Vidhya...

    I would receive files on Monday and Tuesday.

    If file is not present in the share on Monday, i have to copy it on Tuesday. Otherwise i dont have to.

     

    Thanks & Regards,

     

     

    Thursday, February 21, 2008 2:38 PM
  • EXEC xp_cmdshell 'net use Z: \\10.0.0.10\e /user:domain\userName "password"'

    is work

    Monday, May 14, 2018 6:39 PM