Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: xp_cmdshell error Unable to open BCP host data-file

    I have to split an XML file that was too big for our partner to digest in many smaller XML files. Each XML files may only contain 1000 people (elements).

    The table XML_storage contains the lines of an XML-file as records. I added a column to indicate if that line belongs to the Header, Body or Footer of the XML file. To make unique file names, I have to add the time (hh_mm_ss_mss) it was generated in the filename and in the <time></time> element in the header of the XML file.

    When I execute the bcp commands in a DOS box, all works fine, but when I try to execute it from within a stored procedure with EXEC master..xp_cmdshell @bcpCommand, I get this error message:
    Code:
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]
      Unable to open BCP host data-file
    I thought it had something to do with authorisation rights on the network L: drive, but I got the same error when I tried to let it generate the files on the C: drive.

    Basically it genarates a Header file, a Body file and a Footer file, at the end it appends all the files together.

    Code:
    CREATE PROCEDURE dbo.P_KSZ2XMLs (
    	@FileNamePrefix		NVARCHAR(10),	
    	@MaxBlockSize 	BIGINT	= 1000	
    	)
    AS
    BEGIN
    	SET NOCOUNT ON
    
    	DECLARE @DATA_PATH varchar(250)
    	DECLARE @CurrentTimestamp DATETIME	-- CURRENT TIMESTAMP
    	DECLARE	@SQLStr varchar(500)
    	DECLARE @bcpCommand varchar(2000)
    	DECLARE @FirstNr BigInt
    	DECLARE @LastNr BigInt
    	DECLARE @BlockLastNr BigInt
    	DECLARE @FileName varchar(250)
    
    	SET @DATA_PATH = 'C:\MO\Data\KSZ\'
    
    	SET @FileName = @DATA_PATH + '#footer.xml'
    	SET @SQLStr = 'SELECT XML_line FROM IU1.dbo.XML_storage WHERE BlokName = ''Footer'' ORDER BY id'
    	SET @bcpCommand = 'bcp "' + @SQLStr + '" queryout "' + @FileName + '" -T -c -S SRV01/UI'
    PRINT '@bcpCommand = ' + @bcpCommand
    	EXEC master..xp_cmdshell @bcpCommand
    
    	SELECT @FirstNr = min(ElementSeqNr), 
    		@LastNr = max(ElementSeqNr) 
    	FROM IU1.dbo.XML_storage
    	WHERE BlokName = 'Body'
    
    	WHILE @FirstNr <= @LastNr
    	BEGIN
    		SET @BlockLastNr = @FirstNr + @MaxBlockSize - 1
    
    		SET @FileName = @DATA_PATH + '#header.xml'
    		SET @SQLStr = 'SELECT REPLACE(XML_line, ''<Time>hh_mm_ss_mss</Time>'', ''<time>'' + RIGHT(RTRIM(REPLACE(CONVERT(VARCHAR(24), GetDate(), 113) ,'':'', ''_'')),12) + ''</time>'') FROM IU1.dbo.XML_storage WHERE BlokName = ''Header'' ORDER BY id'
    		SET @bcpCommand = 'bcp "' + @SQLStr + '" queryout "' + @FileName + '" -T -c -S SRV01/UI'
    PRINT '@bcpCommand = ' + @bcpCommand
    		EXEC master..xp_cmdshell @bcpCommand
    		
    		SET @FileName = @DATA_PATH + '#body.xml'
    		SET @SQLStr = 'SELECT XML_line FROM IU1.dbo.XML_storage WHERE BlokName = ''Body'' AND ElementSeqNr BETWEEN ' + 
    			str(@FirstNr) + ' AND ' + str(@BlockLastNr) + ' ORDER BY id'
    		SET @bcpCommand = 'bcp "' + @SQLStr + '" queryout "' + @FileName + '" -T -c -S SRV01/UI'
    PRINT '@bcpCommand = ' + @bcpCommand
    		EXEC master..xp_cmdshell @bcpCommand
    
    		SET @FileName = @DATA_PATH + @FileNamePrefix + '-' + RIGHT(RTRIM(REPLACE(CONVERT(VARCHAR(24), GetDate(), 113) ,':', '_')),12)  + '.xml'
    		SET @bcpCommand = 'type ' + @DATA_PATH + '#header.xml' + ' > '  + @FileName
    PRINT '@bcpCommand = ' + @bcpCommand
    		EXEC master..xp_cmdshell @bcpCommand
    		SET @bcpCommand = 'type ' + @DATA_PATH + '#body.xml' + ' >> '  + @FileName
    PRINT '@bcpCommand = ' + @bcpCommand
    		EXEC master..xp_cmdshell @bcpCommand
    		SET @bcpCommand = 'type ' + @DATA_PATH + '#footer.xml' + ' >> '  + @FileName
    PRINT '@bcpCommand = ' + @bcpCommand
    		EXEC master..xp_cmdshell @bcpCommand
    
    		SET @FirstNr = @FirstNr + @MaxBlockSize
    	END
    	SET NOCOUNT OFF
    END
    GO
    
    EXECUTE dbo.P_KSZ2XMLs 'ZPND'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Solved it,

    I changed

    SET @DATA_PATH = 'L:\MO\Data\KSZ\'

    to

    SET @DATA_PATH = '\\servername\MO\Data\KSZ\'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    betcha the drive mapping is yours and not the servers
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •