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
  • Hi everyone!

    I've been trying to solve this problem for 2 days, I made a stored procedure that generates a file in csv, which works well locally as a test, but what I really need is to do it from the server to the machine Of a specific user within the network.
    Investigating, on many other sites I see that they all talk about the user of the sql express service, which is configured as. \ Administrator on the server, and I do not know how I can give permissions to that Administrator on the network and on a folder that is not Locally. (The folder where the csv is to be written is shared and from run-->

     \\IP_userMachine\Users\NameAccountUser\Desktop\Folder\ el servidor ; The server accesses this location successfully)

    Mi SP query is:

    SELECT @vsql= 'bcp "exec [zktime].dbo.PRCGeneraCSV" queryout "\\IP_userMachine\Users\NameAccountUser\Desktop\Folder\archivo.csv" -c -t "; " -S' + @@SERVERNAME  + ' -T'

     EXEC MASTER..xp_cmdshell @vsql

    and error is:

    SQLState = S1000, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
    NULL

    Thank you beforehand whoever can help me ..

    Regards!!!

    PS. my english is very very awful


    Friday, June 30, 2017 4:03 PM
  • I think I see a couple of mistakes here:

    Does this work better?

    declare @vsql varchar(max)
    SELECT @vsql= 'bcp "[zktime].dbo.PRCGeneraCSV" queryout "\\IP_userMachine\Users\NameAccountUser\Desktop\Folder\archivo.csv" -c -t ''; '' -S' + @@SERVERNAME  + ' -T'''
     --EXEC MASTER..xp_cmdshell @vsql
    select @vsql
    

    Friday, June 30, 2017 4:09 PM
  • Hi !

    No, that's what you're doing is loading the variable with a dynamic query and not executing the stored procedure..

    :(

    Friday, June 30, 2017 5:20 PM
  • It is not clear under which service accont your SQL Server instance run under, but if it is Local System, or a service SID like NT SERVICE\MSSQL$EXPRESS, you need to grant the machine account permission on the drive, that is DOMAIN\MACHINE$. This assumes that you are in a domain. If you are in a workgroup, this will be challenging.

    Overall, I am not particularly fond of solutions where you run bcp from inside SQL Server. It may be better to run a CMDExec or PowerShell job from Agent.

    Friday, June 30, 2017 9:13 PM