none
xp_cmdshell 'bcp

    问题

  • EXEC xp_cmdshell 'bcp "exec [DB_Test].dbo.RequestFileOutPut" queryout "\\tcn0010sppp1\common\ICB\RPP\CITYNAME\MY Requests\bcptest.csv"  -U LECSLWISUser -P Password#1 -c -t,'

    I am getting bellow error message any help?

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


    simam

    2012年7月6日 1:01

全部回复

  • Are you sure that the csv files exists and the account SQL Server is running has read-write access to this file?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月6日 1:06
  • Yes it is working perfectly if I used

    queryout "D:\ICB\bcptest.csv"

    I want to save the csv file on other server.

    Any help will be appreciate.

    Thank you


    simam

    2012年7月6日 1:31
  • Well, you need to give the SQL Server account rights to that folder on the other server.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月6日 1:46
  • Can you help me how to give the SQL server account right?

    EXEC xp_cmdshell 'bcp "exec [DB_Test].dbo.RequestFileOutPut" queryout "\\tcn0010sppp1\common\ICB\RPP\CITYNAME\MY Requests\bcptest.csv"  -U LECSLWISUser -P Password#1 -c -t,'

    Thank you, appreciate your help


    simam

    2012年7月6日 2:26
  • This is something you have to discuss with the Network Administrator as it involves giving that user rights to the particular directory on that server.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月6日 2:34
  • You'll have to ensure that the SQL server account has access to the share you are referring to.

    One way of testing it, is to run the below command -

    EXEC xp_cmdshell 'DIR "\\tcn0010sppp1\common\ICB\RPP\CITYNAME\MY Requests\"'

    This should tell you if you have access to the share or not - and until that works, you won't get your BCP to work.


    Steen Schlüter Persson (DK)


    2012年7月6日 6:59
  • Hi Steen,

    I believe the command should be

    EXEC xp_cmdshell 'DIR "\\tcn0010sppp1\common\ICB\RPP\CITYNAME\MY Requests\"'

    and not sp_cmdshell


    Best Regards, Venkat

    2012年7月6日 7:21
  • Sure... that was a mistake - I have corrected it now.

    Thanks for pointing it out..:-).


    Steen Schlüter Persson (DK)

    2012年7月6日 7:26
  • When I run

    EXEC xp_cmdshell 'DIR "\\tcn0010sppp1\common\ICB\RPP\CITYNAME\MY Requests\"'

    I am getting "'DIR"\\tcn0010sppp1\common\ICB\RPP\CITYNAME\MY Requests\"' is not recognized as an internal or external command"

    Not sure what kind of permission and from where. I have already access to go that folder( I was the one who create that folder)


    simam

    2012年7月6日 12:35
  • Can you run this DIR command directly from command line? Make sure it works there first, then run it from SSMS. Your account and account SQL Server is running from can be completely different things.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月6日 13:02
  • Seems like you are missing a space after DIR. Or you have a hard space. Delete whatever is there, add a space a try again.

    Whether you have access to that folder is irrelevant. What matters is the permissions of the account SQL Server runs under.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012年7月6日 13:03
  • It's not you that needs access to the folder or the share  - it's the account that runs the SQL server service.

    You'll have to make sure that this account has the rights to accessing the share on your TCN0010SPPP1 server. If possible, you can log on as this account and then try to access the share/files. That should show you if you have to correct access.


    Steen Schlüter Persson (DK)

    2012年7月6日 13:03