Header and Details to export to file

[LinPro]

Hi
I am trying to export from SQL Server 2005 to textfile or csv file. I created two queries in a stored proc.
This worked partially, but it returns a blank row in between the header and detail.


I need the result to look like this:

Header, acctno, bankno, xxxxxxxx
Detail1, acctno, chkno, chkamt,chkdate, ven_name1
Detail2, acctno, chkno, chkamt,chkdate, ven_name2
Detail3, acctno, chkno, chkamt,chkdate, ven_name3
Detail4, acctno, chkno, chkamt,chkdate, ven_name4
Detail5, acctno, chkno, chkamt,chkdate, ven_name5
Detail6, acctno, chkno, chkamt,chkdate, ven_name6
Detailetc.....


And suggestions?

Lin

 

[mrdenny]

How are you running the stored procedure?

Try changing the queries in the procedure to use a UNION ALL between them instead of having two seperate recordsets.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[LinPro]

Hi

Thanks for the response.

However, if I use union - dont I need to have exact columns for both header and details.

I dont want the headers to be part of the details. I only need one Header and on the first row and rest of the rows to be the details.

Lin

 

[LinPro]

Hi MrDenny

I need the Header and the details to be fixed columns, plus the header must be different from the detail.

Header (20), acctno (13), bankno (4), xxxxxxxx
acctno(13), chkno1(10), chkamt(10),chkdate(8), ven_name1 (50)
acctno(13), chkno2(10), chkamt(10),chkdate(8), ven_name2 (50)
acctno(13), chkno3(10), chkamt(10),chkdate(8), ven_name3 (50)
acctno(13), chkno4(10), chkamt(10),chkdate(8), ven_name4 (50)
acctno(13), chkno5(10), chkamt(10),chkdate(8), ven_name5 (50)
etc

I hope this clarify what I am looking for.

Lin

 

[jbenson001]

This is not a job for SQL, but for a front end application or reporting tool. SQL is for maniuplating data and storing it, not for pretty displays or formatting.

 

[LinPro]

I was not looking for pretty display. I was looking for data to be saved in a flatfile. Please uses stored procedures and views to export to csv or textfile all the time. Are you saying that SQL Server cant handle it? I know Oracle does.

 

[mrdenny]

SQL Won't like trying to put two different recordsets together that don't have the same number of columns. What's the point of a header row which doesn't have a column header for each column in the output?

Export each recordset to a seperate file, then concatinate the files together.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[LinPro]

This point to this is to export this information to the bank for check conciliation.

Aren't other companies doing this?

 

[simian336]

I have a similar application but I do it in VB.

But, You could combine your fields into a single string.

I tried it with one of my test tables and it seems to work.

 

[simian336]

Sorry, somthing in my code is messing up the posting...

Simi

 

[simian336]

I was trying to post the code as well.

Concatinate all your fields in to one string field and union to the header.

Simi

 

[LinPro]

thanks Simi

but I have decided to create this in Crystal and view it in Crystal viewer than export to Text.

I don't like SQL Server very much. I do know I can do it in Oracle, however this company is not using that.

Lin