войти
 
ГлавнаяНовостиБиблиотекаОбучениеСкачатьМероприятияСообществоФорумыПоддержкаО проекте
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
Ресурсы для ИТ-профессионалов >
> 
> 
MS SQL 2008 BCP error

Отвечено MS SQL 2008 BCP error

  • 8 июня 2012 г. 9:49
     
     
    Нужно войти
    0

    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 г. 10:03
     
     Отвечено
    Нужно войти
    0
    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:24
     
     Отвечено
    Нужно войти
    0

    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:50
    Отвечающий
     
      С кодом
    Нужно войти
    0

    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 г. 22:14
    Модератор
     
     
    Нужно войти
    0
    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

     
  • 18 июня 2012 г. 7:23
     
     
    Нужно войти
    0
    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.