driving me crazy

• henry-598187

  Newbie

  Points: 9

  More actions

  February 7, 2008 at 2:19 am

  #61493

  I get this error everytime I run the following Stored Procedure

  SQLState = HY000, NativeError = 0

  Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

  NULL

  declare @bcpCommand varchar(2000),

  @filename varchar(50),

  @bcpSQL varChar(100)

  --set @bcpCommand = 'bcp "select [RecID],[Fname],[Lname],[Result],[TargetVal],[LwrTolVal],[UprTolVal],[Material],[TestDate],[CharacText],[Worker],[Plant],[WsID],[ActivNum],[CharacSeq],[SampleNum],[TestTime],[Line],[Cutter],[Type],[ShiftCrew],[TestCount],[Attribute],[ResultText],[RecordingType],[ResultSummary],[DumpDate]'

  set @bcpCommand= 'bcp "SELECT *'

  set @bcpSQL = ' FROM pilgrimsSpc..QAdump ORDER BY charactext" queryout "'

  set @filename=Replace('c:\qadump1.txt','/','-')

  set @bcpCommand=@bcpCommand+@bcpSQL+@filename+'" -T -c -t,'

  print @bcpCommand

  exec master..xp_cmdshell @bcpCommand

  And I have tried it with all fields in the statements

  It also works fine when I run the Results from the print@bcpCommand in the cmd prompt.

  If anyone has any clues please let me know.

  Thanks,

  Henry

• Greg D-550251

  Old Hand

  Points: 300

  More actions

  February 7, 2008 at 2:48 am

  #775915

  From SQL Server BOL "The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account."

  So make sure the account that starts up SQL Server has access to the destination.

  Greg