Extracting multiple results to one txt file 1

[E3xtc]

Hi,

hopefully someone can offer some sense on this - I am wanting to extract the results of 2 seperate queries into one txt file.
However the results in the txt file need to retain their specific field lengths.

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.

Thanks in advance for your assistance on this.

Regards
Troy

 

[hmckillop]

In my opinion, this sounds like an application development project rather than a data output project.
Essentially you are trying to manipulate the format of 3 queries into a specific output and SQL wasnt build for that.
Personally speaking I would build a small VBA or VB app to do this and you could make it in no more than an hour.

As far as doing it in SQL, you could output the data as XML and then use some XSLT to generate the output.
Other than that, not sure based on what you have specified

"I'm living so far beyond my income that we may almost be said to be living apart

 

[E3xtc]

Hmm thanks for the response - as I feared what you have said...but alas programming is not a skill I have....I may have to get someone in for this one...

Thanks again for your response!

 

[hmckillop]

its really not complicated, I am sure if you posted on one of the VB forums or VBA, someone would give you a snippet of code which would get the results from a db and put it into a text file.
If you dont get a response on the VB forum, post here again and I will drop some code later tonight, just dont have time at the minute.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[E3xtc]

Thanks hmckillop - I have posted something there, and will await and see if anyone can offer some feedback....

Fingers crossed - will post a response either way...

Thanks again for your help.

 

[hmckillop]

the code should end up something similiar to this

Dim cn As New ADODB.Connection
Dim rsHeader As New ADODB.Recordset
Dim rsLineItems As New ADODB.Recordset
Dim rsTotals As New ADODB.Recordset


'if not trusted connection
'cn.ConnectionString = "Provider=SQLOLEDB;Data Source=my_server;Initial Catalog=mydb;User Id=sa;Password=;"
'if trusted connection use next connection string
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=my_server;Initial Catalog=mydb;Integrated Security=SSPI;"
cn.Open

Set rsHeader = cn.Execute("Select * from recordset1")
If rsHeader.EOF <> True Then
    Open "c:\TESTFILE.txt" For Output As #1    ' Open file for output.
    Write #1, rsHeader(0), rsHeader(1), rsHeader(2), rsHeader(3), rsHeader(4), rsHeader(5)           ' Write comma-delimited data.
   

End If
Set rsLineItems = cn.Execute("Select * from recordset2")
While Not rsLineItems.EOF
    Write #1, rsLineItems(0), rsLineItems(1), rsLineItems(2), rsLineItems(3), rsLineItems(4), rsLineItems(5)           ' Write comma-delimited data.
    rsLineItems.MoveNext
Wend

If rsTotals.EOF <> True Then
    Write #1, rsTotals(0), rsTotals(1), rsTotals(2), rsTotals(3), rsTotals(4), rsTotals(5)           ' Write comma-delimited data.
End If

 Write #1,    ' Write blank line.
    Close #1    ' Close
cn.Close

you may need to change how you output to text file etc, but if you cut and paste this into Excel VBA module, and add a reference to Microsoft Active Data objects (version 2.1 or above). it should work.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[E3xtc]

Hey - thanks for that! I have had a bit of a play and put that into a DTS package - made some minor changes to the connection strings/T-SQL Selects, so that everything "looks" right....but when I parse the script it says:

Microsoft VBScript Compilation error
Expected end of statement

I have put this code into an ActiveX Script Task in DTS - could this be where the problem is at?

Sorry for my inept lack of knowledge on this - but very keen to hear back....

Thanks
Troy

 

[E3xtc]

Sorry forgot to mention that error is generated on the line which has :

Open "c:\TESTFILE.txt" For Output As #1 ' Open file for output.

Thanks
Troy

 

[hmckillop]

Sorry I am not too useful with DTS, not sure if "Open for output" is valid within DTS.
Will post back when i investigate some more later, or alternatively post the error on the SQL admin forum and someone might be able to help.


"I'm living so far beyond my income that we may almost be said to be living apart