exporting to CSV

Hi,

I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms

Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal

1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)

So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.

I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.

Thanks for your time and help
:)

.

Relevant Pages

  • Re: exporting to CSV
    ... trying to find a good way of outputting to csv with using either sql ... first way i tried was creating a record set in asp and then using the ... I used bcp using the master.dbo.xp_cmdshell command to output the ... The problem with that is that the bcp executes the stored proc ...
    (comp.databases.ms-sqlserver)
  • Re: exporting to CSV
    ... What you can do is generate your DTS package and schedule it. ... entered as params and output for them a csv file. ... I used bcp using the master.dbo.xp_cmdshell command to output the ... The problem with that is that the bcp executes the stored proc ...
    (comp.databases.ms-sqlserver)
  • Re: exporting to CSV
    ... entered as params and output for them a csv file. ... first way i tried was creating a record set in asp and then using the ... I used bcp using the master.dbo.xp_cmdshell command to output the ... The problem with that is that the bcp executes the stored proc ...
    (comp.databases.ms-sqlserver)
  • Re: Calculated Columns vs Calculations in stored procs.... what is the best way ???
    ... 99% of reports will not update the database). ... >> I have several crossTab type reports in a VB app. ... >> stored proc accepts parameters such as onRows, onCol, sumBy, etc.) ...
    (microsoft.public.dotnet.framework.adonet)
  • Automatic log on to website and download report using VBA
    ... I have to pull out some reports from my company's intranet web-site (a ... reporting system) in CSV format on a regular basis and this CSV file is ...
    (microsoft.public.excel.programming)