Assistance with some exporting to txt code?

[E3xtc]

Hi All,

I have posted this in the SQL threads and they suggested to post here on the off chance someone could offer some code which would do this a gem

Basically I am wanting to extract the results of 2 seperate queries (which could be either a SP or a direct T-SQL string) into one txt file.
However the results of the 2 extractions, in the txt file need to retain the specific field lengths from the respective extracts.

The file is essentially a buildup of an order file which has line 1 = header information, and line 2+ = line items on the order. Finally the 3rd line is just a sum of the quantities in the line items.

I had thought about putting all this information into the one table and exporting the contents, however the field lengths are different on each line so this idea wouldn't work - as these field lengths need to be retained as they will form part of the structure of the fixed width delimited txt file.

I have been given the options of merging the 2 files using DOS however I have also been informed that this can introduce some unreliable results - which cannot be handled where this file is going.
Another option is bcp - which at this stage, seems to be the only realistic option - however I was interested to see if others confirmed that this is the only/best way, or whether there is something else which might be better suited.

Hopefully I have given enough information on what I am trying to achieve - if I need to offer more detail, please let me know.

So if anyone is able to assist a non-programmer with some code which I could improvise with in order to massage it into our SQL environment (DTS package with Active-X script), then this is hugely appreciated.

Thanks in advance for your assistance on this.

Regards
Troy

 

[Sheco]

You could put it into an ADO recordset and then persist it to an XML file and then do a transform on the file.

Also, if all of the fields are text data types... I mean even the time/dates and numeric fields are saved in char, varchar, etc... then you could do something like this:

For Each oFld in rs.Fields
sOutput = sOutput & Left(oFld.Value & Space(oFld.DefinedSize), oFld.DefinedSize)
Next