exporting to CSV

[Katie]

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
:)

[Cimode]

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
:)

[Katie]

How would that work if i want the user to enter values in a form and
then wen they click submit run a stored proc using the values they
entered as params and output for them a csv file.

Cimode wrote:

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
:)

[Brian Peasland]

Katie wrote:

How would that work if i want the user to enter values in a form and
then wen they click submit run a stored proc using the values they
entered as params and output for them a csv file.

You can write a custom DTS package that is called from a stored proc.
Parameters to that stored proc will be the parameters you want to query
on. The stored proc will set global DTS variables based on these
parameters and DTS will use these variables to filter out the unwanted
records. You'll probably want a good book on DTS as this sort of thing
can be a bear for the uninitiated. SQL Server 2000 DTS Step By Step by
Carl Rabeler worked for me!

Cheers,
Brian
--
================================================== =================

Brian Peasland
or********@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

[Cimode]

What you can do is generate your DTS package and schedule it. As a
result you will get an identifier for that package. You may then call
the package from some user event.

Katie wrote:

How would that work if i want the user to enter values in a form and
then wen they click submit run a stored proc using the values they
entered as params and output for them a csv file.

Cimode wrote:

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
:)