Hello,

We have a client/server system which utilizes SQL Server 7
and the internet. However, when very large numbers of rows
are retrieved, it is very time consuming.

An example: one set of data retrieved is 41MB. If that
file is converted (exported) to a comma or tab delimited
file, the new file size is 12MB. If the delimited file is
then zipped (using WinZIP), the final file size is 700K.
Of course, we would much rather transfer the final file
over the internet.

I'm not sure whether DTS can help with this, and also
would appreciate any help with delivering the compressed
file to the client side (a Windows workstation). For
example, does the zipped file have to be placed in the
database or is there another way to transfer the file?

Thank you,

Mike T.

In article <082a01c37173$ab2e93c0$a001280a (AT) phx (DOT) gbl>, Mike T.
<m.tamburro (AT) technologue (DOT) com> writes
I am unclear on the full requirements, but bear in mind that DTS is a
client side tool. So you could write a DTS package that extracts data
from your remote SQL DB, and writes it to CSV. You can then Zip this
file and mail it or FTP or such like to the recipient.

If you run this package from your local workstation it defeats the
point. If you run it via remote console tool or via SQL Server agent
actually on the remote DB server then you are OK.

Does this help at all?

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Sorry about the original message. Let me attempt to
clarify the process we would like to implement. Here's how
we would like it to work:

1) The client software submits a query request. The
request is not a simple query, but a series of queries
which are all processed at the server - no traffic on the
wire yet.

2) The final result set can be huge and must now be
transferred from the server to the client. This is our
problem - it simply takes too long to be practical. So, we
would like to:

3) Export the result set to a comma delimited file, which
compresses all the blank fields.

4) Zip (or otherwise compress) the comma delimited file to
make the file smaller still.

5) Transfer the zip file over the wire to the client,
which will unzip, import, etc. and present to the user.

Hope this helps and any guidance is much appreciated!

Thank you,

Mike

In article <082a01c37173$ab2e93c0$a001280a (AT) phx (DOT) gbl>, Mike T.
<m.tamburro (AT) technologue (DOT) com> writes

Is this an interactive application? If so then I would question why you
are transferring so much data to the client.

If this is a reporting type function which could be considered offline
as far as a user is concerned then it may be possible. You could write a
system where by you insert a query into a table on the remote server.
The remote server, the one with all the data on, could then have a
scheduled job that checks for new rows in the table. When found it grabs
the query and loads a DTS package. Enters the query as a source for the
package, and then executes the package. The package writes the data to
disk, zips it up then ftps or https he zip file down to the client.

This would be a very complicated process to manage, and I am still not
convinced it is viable, but it is the best I can think of from what I
know.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

I wrote a very similar job for a product I'm selling. Here is an outline of the tasks in that package.
1.. Dynamic Properties Task
a.. lets some Create Process tasks know where to find their files, based on GVs
2.. Execute SQL Task
a.. prepares the result set - this is where you would fire off a stored procedure to run all those queries of yours
3.. ActiveX Task
a.. I'm using some other tool (which I fire off using a bit of vbScript) to create the text output
b.. you would probably just have a Transform Data Task here
4.. ActiveX Task
a.. this is where I build a little batch file for zipping
b.. it calls to winzip command-line; in the past I've also used freezip
5.. Execute Process Task
a.. this is what executes that batch file to zip up the exported text file of all those records
b.. be sure to use UNC paths
6.. ActiveX Task
a.. this is where I build a little batch for the ftp and a little text file for the ftp commands
7.. Execute Process Task
a.. this is what executes that ftp batch file (using the commands in the text file)
b.. be sure to use UNC paths
I have some other steps in there, for clean-up and archiving, and the most of the steps are configurable by changing GVs, but you get the point. Obviously, there are other approaches to this; for examply, steps 4-7 probably could be done all in one ActiveX script or in a stored procedure.

Best regards,



JOH



"Mike T." <m.tamburro (AT) technologue (DOT) com> wrote