CSV output sql server 2000

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
:)You can use the OSQL utility in order to create a file with or without
headers (you can control the behavior of the headers with the -h
switch). Another tool that you can use is DTS package and run it with
DTSRun utility. Also I would not run the tools in a stored procedure
with xp_cmdshell, because this is a huge security problem. By working
this way you give the user a way to run there own scripts and command
on the operating system level with the privileges of the account that
runs SQL Server's service. Since you are working with ASP anyway, it
is better to run the OSQL (or if you choose a different utility, then
the other utility) from the ASP code and not from a stored procedure.
Adi
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
> :)|||Hi,
Thanks for your response.
But isnt OSQL also a command line utitlity? how would i run that from
inside asp. Would i use some shell command utility in vbscript?
:)
Adi wrote:
> You can use the OSQL utility in order to create a file with or without
> headers (you can control the behavior of the headers with the -h
> switch). Another tool that you can use is DTS package and run it with
> DTSRun utility. Also I would not run the tools in a stored procedure
> with xp_cmdshell, because this is a huge security problem. By working
> this way you give the user a way to run there own scripts and command
> on the operating system level with the privileges of the account that
> runs SQL Server's service. Since you are working with ASP anyway, it
> is better to run the OSQL (or if you choose a different utility, then
> the other utility) from the ASP code and not from a stored procedure.
> Adi
>
> 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
> > :)|||Hi Katie
I'm sorry, but I'm not an ASP developer. As far as I know, you can
run a command line utility from an ASP code with the WScript.Shell. I
admit that I don't know much more (for example if this also has some
kind of security implications, or if there are some limitation of using
the WScript.Shell object, etc') and it would be a good idea to get more
information about running a command line utility from the ASP code.
Adi
Katie wrote:
> Hi,
> Thanks for your response.
> But isnt OSQL also a command line utitlity? how would i run that from
> inside asp. Would i use some shell command utility in vbscript?
> :)
> Adi wrote:
> > You can use the OSQL utility in order to create a file with or without
> > headers (you can control the behavior of the headers with the -h
> > switch). Another tool that you can use is DTS package and run it with
> > DTSRun utility. Also I would not run the tools in a stored procedure
> > with xp_cmdshell, because this is a huge security problem. By working
> > this way you give the user a way to run there own scripts and command
> > on the operating system level with the privileges of the account that
> > runs SQL Server's service. Since you are working with ASP anyway, it
> > is better to run the OSQL (or if you choose a different utility, then
> > the other utility) from the ASP code and not from a stored procedure.
> >
> > Adi
> >
> >
> > 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
> > > :)|||Hi,
I tried the OSQL and was able to output a text file but the problem
with that was that it was not formatted. It included blank spaces in
the column (wasnt trimmed) and also the column headers where followed
by a -- line
Unfortunately excel wouldnot import the OSql text file correctly
:(
Adi wrote:
> Hi Katie
> I'm sorry, but I'm not an ASP developer. As far as I know, you can
> run a command line utility from an ASP code with the WScript.Shell. I
> admit that I don't know much more (for example if this also has some
> kind of security implications, or if there are some limitation of using
> the WScript.Shell object, etc') and it would be a good idea to get more
> information about running a command line utility from the ASP code.
> Adi
>
> Katie wrote:
> > Hi,
> >
> > Thanks for your response.
> > But isnt OSQL also a command line utitlity? how would i run that from
> > inside asp. Would i use some shell command utility in vbscript?
> >
> > :)
> >
> > Adi wrote:
> > > You can use the OSQL utility in order to create a file with or without
> > > headers (you can control the behavior of the headers with the -h
> > > switch). Another tool that you can use is DTS package and run it with
> > > DTSRun utility. Also I would not run the tools in a stored procedure
> > > with xp_cmdshell, because this is a huge security problem. By working
> > > this way you give the user a way to run there own scripts and command
> > > on the operating system level with the privileges of the account that
> > > runs SQL Server's service. Since you are working with ASP anyway, it
> > > is better to run the OSQL (or if you choose a different utility, then
> > > the other utility) from the ASP code and not from a stored procedure.
> > >
> > > Adi
> > >
> > >
> > > 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
> > > > :)