Posted by Cimode on 07/07/06 08:49

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

[Back to original message]