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

    Unanswered: problem with BCP

    An existing DTS package (SQL Server 2000) generates a number of records in a table called XML_storage. Each record stores the content of an XML-line (like '<Request>', ' <Name>John Doe</Name>', ...). When one exports the column XML_line of all the records into a file, ordered by id, you get an XML-file.

    The data volume has grown, and now we must limit the number of body elements to a maximum of 1000 per XML-file. So instead of generating 1 huge XML-file, we now have to generate multiple smaller ones.

    I wrote a SP that exports the header part of the XML ('<?xml version="1.0" encoding="UTF-8"?>', ...) to a file called #header.xml. I did the same with the footer part, #footer.xml.
    I a while loop I extract the XML lines belonging to the next 1000 body elements and export them to the file #body.xml. Then I "glue" those files together in the destination file, using a sequence number to generate unique file names. Basically the glueing is done by these commands:

    type \\My\Server\path\#header.xml > \\My\Server\path\FileName_0001.xml
    type \\My\Server\path\#body.xml >> \\My\Server\path\FileName_0001.xml
    type \\My\Server\path\#footer.xml >> \\My\Server\path\FileName_0001.xml


    When I execute this SP from within SQL Query Analyzer, all works fine.
    When I execute this SP from within a DTS Execute SQL Task, it generates the #header.xml, #body.xml and #footer.xml files, but does not generate the final XML-file. Somehow it stops, because the code following the generation of the final XML-file, is also not executed.

    (***) I have added return-code checking code, but I don't get any error message indicating where or what went wrong.
    Code:
    WHILE ... 
    BEGIN	
    	SET @SequenceNr = @SequenceNr + 1
    	SELECT @SequenceNrStr = RIGHT('0000'+ CAST(@SequenceNr as VARCHAR(4)), 4)
    	
    	SET @FileName = @DATA_PATH + '#body.xml'
    	SET @SQLStr = 'SELECT XML_line FROM dbname.dbo.XML_storage WHERE BlockNr = 2 AND ElementNr BETWEEN ' + @FirstProofNr + ' AND ' + @LastProofNr + ' ORDER BY id'
    	SET @bcpCommand = 'bcp "' + @SQLStr + '" queryout "' + @FileName + '" -T -c -S My\Server'
    	EXEC @ReturnCode_xp_cmdshell = master..xp_cmdshell @bcpCommand
    	IF (@ReturnCode_xp_cmdshell <> 0) -- (***)
    	BEGIN
    		RAISERROR ('bcpCommand1 "%s" returns an error.', 16, 1, @bcpCommand	)
    		RETURN
    	END		
    
    	SET @FileName = @DATA_PATH + @FileNamePrefix + '_' + @SequenceNrStr + '.xml'
    	SET @bcpCommand = 'type ' + @DATA_PATH + '#header.xml' + ' > '  + @FileName
    	EXEC @ReturnCode_xp_cmdshell = master..xp_cmdshell @bcpCommand
    	IF (@ReturnCode_xp_cmdshell <> 0) -- (***)
    	BEGIN
    		RAISERROR ('bcpCommand2 "%s" returns an error.', 16, 1, @bcpCommand	)
    		RETURN
    	END		
    
    	SET @bcpCommand = 'type ' + @DATA_PATH + '#body.xml' + ' >> '  + @FileName
    	EXEC @ReturnCode_xp_cmdshell = master..xp_cmdshell @bcpCommand
    	IF (@ReturnCode_xp_cmdshell <> 0) -- (***)
    	BEGIN
    		RAISERROR ('bcpCommand3 "%s" returns an error.', 16, 1, @bcpCommand	)
    		RETURN
    	END		
    
    	SET @bcpCommand = 'type ' + @DATA_PATH + '#footer.xml' + ' >> '  + @FileName
    	EXEC @ReturnCode_xp_cmdshell = master..xp_cmdshell @bcpCommand
    	IF (@ReturnCode_xp_cmdshell <> 0) -- (***)
    	BEGIN
    		RAISERROR ('bcpCommand4 "%s" returns an error.', 16, 1, @bcpCommand	)
    		RETURN
    	END		
    
    	....
    END
    I am also open for suggestions of better ways to do this.
    Last edited by Wim; 05-11-11 at 02:57. Reason: corrected > vs >> in type \\My\Server\path\#body.xml >> \\My\Server\path\FileName_0001.xml part
    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
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It may be a typo, but to append to a file, I think it is >> rather than >. As for the rest, that will take some pondering....

    EDIT: Ahh. Looks like the >> vs > question is correct in the code.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Rather than putting together the file using BCP, how about using PERL, or .NET? Any time I see something where a person is just dumping rows to a file that needs to be formatted, I start thinking about solutions in PERL. But that may just be me. Try this stub of a program, and see if it gets you started. You would need an installation of PERL from ActiveState to run it on windows. And PERL purists would be horrified at my using VB like routines in here, but it does what you seem to need with a relative minimum of fuss.

    Code:
    use strict;
    use OLE;
    
    my $conn = CreateObject OLE 'ADODB.Connection';
    my $rs = CreateObject OLE 'ADODB.Recordset';
    my $server = "server\\instance";
    my $db = "Applications";
    
    $conn->open ("provider=SQLOLEDB;Server=" . $server . ";Database=" . $db . ";Integrated Security=SSPI;Application Name=Perl Export Script");
    if ($conn->errors->count > 0)
     {print "There was an error connecting to the database server:\n";
      print "Error message: " . $conn->errors->item(0)->description . "\n";
      exit 1;
     }
    
    $rs->open("select xml_blob from test1 order by id", $conn);
    if ($conn->errors->count > 0)
     {print "There was an error running the query:\n";
      print "Error message: " . $conn->errors->item(0)->description . "\n";
      exit 2;
     }
    
    open (FILE, ">>test1.xml");
    
    while (!$rs->eof)
     {print FILE $rs->fields(0)->value . "\n";
      $rs->movenext();
     }
    
    $rs->close();
    $conn->close();
    close (FILE);

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thank you for your reaction.

    Let me explain why I reverted to a SP. SQL Server 2000 and DTS is a dead end. It's impossible to take DTS courses any more, so I am stuck with looking how others have solved problems in existing DTS-packages and mimic them.

    The existing DTS packages are good at generating output to one file with a fixed name and location. The calling batch program copies and renames the file to whatever directory it must be located.

    Now I have to generate a variable amount of files, all with a unique name, and I have no example on how to tackle that problem from within DTS. So I resorted to something I know how to program, a stored procedure. And it seemed to work perfectly at first.

    The DTS-package generates multiple XML-files, all in the same manner: first it truncates the table XML_storage, then it inserts the XML-lines into it, then it writes the content of XML_storage to a fixed file. So whatever method I use, I must be able to call that code multiple times from within DTS, because the content of XML_storage will be lost after the next TRUNCATE. If I would comment TRUNCATE out, there is no way of telling what lines/records belong to what XML-file.

    About writing it in PERL, ... . Wait, I've heard of that, isn't that a programming language?
    Even if I could get your sample PERL program to work (I'm better in Python), I don't think I could call that procedure from within DTS.
    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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I haven't tested this in years, but if you use the ActiveState Perl installation Perl becomes a programming language choice in both DTS and SQL Agent.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You should be able to call almost any program by using the Command Line widget in DTS. Naturally, paths and permissions would have to be set up correctly (one of the things I most hated about DTS). As for how to loop through, I am not certain, as I have not used DTS in years, myself. Any chances of getting this ported over to SSIS?

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I've been Googling a lot to find out how I could write Python scripts that get executed within a DTS-package. I found a lot of questions about it, but zero answers.

    > You should be able to call almost any program by using the Command Line widget in DTS.

    I could not find that, but I did found Execute Process Task. That could do the trick. Just need to find out how to pass parameters and return codes.

    > Any chances of getting this ported over to SSIS?

    Yes, we are planning to rewrite the whole thing on MS SQL Server 2008 R2, but that won't happen before the end of the year.
    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

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by Wim
    Yes, we are planning to rewrite the whole thing on MS SQL Server 2008 R2, but that won't happen before the end of the year.
    Always the way, eh? ;-)

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    epilogue

    I have found a solution for the problem. I know DTS and SQL Server 2000 are end-of-life, but this may help some other poor guy with a deadline and no time to investigate it.

    I found that as soon as I defined a parameter in a DTS Execute SQL Task, the call to my SP would fail to generate the XML files.
    EXECUTE dbo.P_MySp2XMLs ?, 'Test'

    Even deleting all parameters afterwards didn't help.

    One actually has to create a fresh new Execute SQL Task and type anew
    EXECUTE dbo.P_MySp2XMLs 'Test'
    if you want it to work (without the "?" for the parameter).

    As soon as you type a "?", the DTS generates "something" behind the scenes to accommodate the parameter and that "something" somehow prevents the SP from functioning properly. That at least is how I interpret it. Perhaps someone else on this forum has a better explanation for it. In any case, as soon as I typed a "?" in the SQL script and even removed it right after it was typed, the call would fail.

    This had the effect that the interface to the SP had to be altered (converting the parameter to an optional one, with a default value = 0). I also had to alter the SP so it would get the value of that parameter without me passing it directly to it. Fortunately I could use
    Code:
    If @EvilParam = 0 
      SELECT @EvilParam = MAX(Id) FROM DaTable
    Ugly.
    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

Posting Permissions

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