|
|
Posted by Cimode on 07/06/06 14:41
use DTS export wizard...then schedule it...
Katie wrote:
> 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
> :)
Navigation:
[Reply to this message]
|