none
MS SQL 2008 BCP error

    Вопрос

  • I'm trying to run on ms SQL  2008

    declare @bcp_command varchar (5000)
    set @bcp_command = 'BCP "select top 1 cid_dossier from d_dossier" queryout "\\server1\bof\zmazat.dat" -T -c -C RAW -S' + @@ServerName 

    EXEC master..xp_cmdshell @bcp_command

    but I'm getting error:

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

    I have RW access to that shared folder.

    ON the same DB on sql 2005 everything works fine...(we actually migrated it to 2008)

    edit: is there another utility instead of BCP, which is recommended to use in sql 2008? I had heard, that BCP isn't recommended to use in 2008 anymore

    Solution: THX everybody for answer. The problem were access rights to the shared server folders.



    • Изменено tomas.t 11 июня 2012 г. 7:45 solution
    8 июня 2012 г. 9:49

Ответы

  • check that the path is correct, that the file is there, that the file isn't open by another process, that SQL Server service account has access/permissions to the share and the file


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 7:10
    8 июня 2012 г. 10:03
  • As Andrew says, it's the permission of the SQL Server service account that matters, not your permissions.

    I am not aware of any deprecation of BCP. However, I am not particularly enthusiastic of using xp_cmdshell. I think it is a better option to create files from a client program. An alternative if you want to perform the export from within SQL Server is to write a CLR stored procedure in C# or VB.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 7:10
    8 июня 2012 г. 10:24

Все ответы

  • check that the path is correct, that the file is there, that the file isn't open by another process, that SQL Server service account has access/permissions to the share and the file


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 7:10
    8 июня 2012 г. 10:03
  • As Andrew says, it's the permission of the SQL Server service account that matters, not your permissions.

    I am not aware of any deprecation of BCP. However, I am not particularly enthusiastic of using xp_cmdshell. I think it is a better option to create files from a client program. An alternative if you want to perform the export from within SQL Server is to write a CLR stored procedure in C# or VB.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 7:10
    8 июня 2012 г. 10:24
  • Hi!

    Below are BCP format;

    EXEC master..xp_cmdshell 'bcp "YourDatabaseQuery" queryout "YouCSVFilePath" -c -t, -S "ServeName\InstanceName" -U bcpuser -P bcppassword -w'
    EXEC master..xp_cmdshell 'bcp "SELECT EmployeeID,NationalIDNumber FROM AdventureWorks.HumanResources.Employee" queryout "E:\Test\Sample.csv" -c -t, -S SSSLT105\SQLEXPRESS08 -U sa -P 123456 -w'
    EXEC master..xp_cmdshell 'type "YourInitailCSVFile" >> "YourAppendedCSVFile"'
    EXEC master..xp_cmdshell 'type "E:\Test\Sample.csv" >> "E:\Test\SampleAll.csv"'
    
    EXEC master..xp_cmdshell 'bcp "YourDatabaseQuery" queryout "YouXMLFilePath" -f formateFile-c..xml -T, -S "ServeName\InstanceName" -U bcpuser -P bcppassword -w'
    EXEC master..xp_cmdshell 'bcp "SELECT EmployeeID,NationalIDNumber FROM AdventureWorks.HumanResources.Employee" queryout "E:\Test\Employee.xml" -f Employee-c..xml -T -S SSSLT105\SQLEXPRESS08 -U sa -P 123456 -w'

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

    8 июня 2012 г. 10:50
    Отвечающий
  • What happens if you use the actual drive's name instead of UNC path?

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


    My blog

    8 июня 2012 г. 22:14
    Модератор
  • with actual drive's letter it worked. As I wrote in mi first post, the problem were the access rights, sys admin has to double check it, because they were forced back to previous state, as he set the access rights at the first time.
    18 июня 2012 г. 7:23