none
BCP Utility via xp_cmdshell and Network Drive

    Question

  • Hi, 

    I need to save a table data in a file in another server else the server sql is running.

    I'm trying to use the BCP utility via xp_cmdshell resource. I've mapped a network drive pointing to the server the file must be saved, but when I try to run the BCP command proving the path to the file using the network drive, the following error is raised:

    *****************************************************************************************

    Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file

    *****************************************************************************************

    When the command is run providing an local system file path, it is executed successfully.

     

    Bellow I'm providing the command i'm trying to execute:

    **********************************************************************************************

    declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out Z:\temp\sysobjects.txt -c -t, -T -S'+ @@servername 

    exec master..xp_cmdshell @sql

    *************************************************************************************************************

    Where Z is the driver letter to the mapped network drive.

    I'd like to know if there is a way to provide the username and password the command could use while trying to save the file in the network drive?

    Friday, April 29, 2011 1:23 PM

Answers

  • Marcus,

     

    When you have to put file file like BCP result, or a backup in a remote drive, just map this drive into windows don´t work, it must be mapped on SQL Server to!, to do this, try some link like this:

     

    exec xp_cmdshell 'net use p:\ \\Server\Folder\Folder\Folder\ /Domain\Login /Password'


    ------------------------------------------------------------- Oracle OCA11g
    Saturday, April 30, 2011 1:52 PM

All replies

  • When you run the bcp utility the mapped drive is attached to your account and credentials.

    xp_cmdshell will be running by default under the serivce account sql server is running under, so this is the account that needs to be permissioned (you should be able to use a UNC path instead of having to set up a mapped drive ).

     


    ajmer dhariwal || eraofdata.com
    Friday, April 29, 2011 4:48 PM
  • Hey Ajmer,

     

    Thanks so much for your reply. Assuming to use UNC path how could I provide login crendentials in the command specification?

     

    Best Regards

    Friday, April 29, 2011 5:06 PM
  • If you permission the sql server service account to have the relevant permissions on the UNC path then you will not need to provide any login credentials.

    Also check out sp_xp_cmdshell_proxy_account.



    ajmer dhariwal || eraofdata.com
    Friday, April 29, 2011 8:57 PM
  • Marcus,

     

    When you have to put file file like BCP result, or a backup in a remote drive, just map this drive into windows don´t work, it must be mapped on SQL Server to!, to do this, try some link like this:

     

    exec xp_cmdshell 'net use p:\ \\Server\Folder\Folder\Folder\ /Domain\Login /Password'


    ------------------------------------------------------------- Oracle OCA11g
    Saturday, April 30, 2011 1:52 PM