All:



I've got a scenario where I need to export SQL tables to MS Access and
then allow a user to download them. A couple of questions:

1) Is DTS the way to go, even if the environment is a shared hosting
environment?

2) If so, how in the world would I deal with communication between the
web and SQL server (please be as detailed as possible)



Thanks in advance


--
Posted via http://dbforums.com

Without knowing all the details of the scenario, this will probably be a
little vague.

My first reaction is not to use DTS for this solution. Mainly because there
are going to be a number of things that need to be done that DTS isn't realy
good at, and you may have limited rights on the SQL server. If, however, it
must be done with DTS, I'd suggest creating a template database in access as
the destination for the data pumps. Copy the template to a new file at the
beginning of the pakage with a unique name, dynamically assign the
destination of all the DataPump tasks via activex or dynamic properties
tasks and pump in the data. The job can be executed from the web server by
executing xp_cmdshell.

The biggest problem I see here is that dts packages have a tendancy to run
longer than the standard timeout on the browser, so you may have to set up a
queueing system as another table that contains the requests for data
populated real time with a scheduled job that comes through and creates the
various MDBs. The other issue you will most likely run into is contention
for resources on the SQL server as this is a shared environment. My best
suggestion, sans queueing, is to follow the same basic track as I mentioned
in the DTS solution, but create the mdb on the web server and populate it
via ADO.

Hope this helps and best of luck on your project!

Robert Hamron


"robrichard" <member47016 (AT) dbforums (DOT) com> wrote

In article <#887vy6oDHA.2232 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, Robert Harmon
<harmonr@n.o.invalid> writes

I probably wouldn't use DTS in a shared hosting environment, but for
different reasons. Normally the web and sql are separated across two
machines and you do not have file access between the two, so this means
the DTS package must run on the web server so that file is written to
the web space ready for download. I would not expect the web server to
have the DTS objects installed, hence it cannot be done. If DTS was
available on the web server then I probably would use it, as the
time-out issue can be overridden in ASP, and I don't think Access will
be any faster so the same override would be required either way.

The mdb template option could offer some benefits for both methods, is
not necessary for either. You can create an empty mdb through ADOX and
then issue a simple CREATE TABLE statement. This point is really a case
of what works best for you.

Contention issue would also affect the web server in shared hosting as
well as the SQL, and there are too many unknowns I think for you to make
that call with testing it over time.

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

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org