Okay -

Here's the situation, and let me know what you think is
the best approach, all ye SQL gurus. ;-)

- Our Clients have requested a feature that allows them to
get all their data in SQL Server format (as SQL Data
files).
- We're trying to figure out the best way to do this.
We're not giving the Client direct access to this feature,
but rather if they request it, then I'd like to run a DTS
package (or perhaps a Sproc) and give it only the
parameter ClientID=XXXXXXX. Are ya with me so far?
- This DTS package should get all the data (from a couple
hundred tables that are pretty well normalized) from one
DB, and then push it to a database that the Client can
then go download from an FTP site.

Based on that information, what approach would you take?
I'm new to advanced DTS packages, so please give a step-by-
step approach if you can... Let me know if you need more
info...

TIA,
Aaron

What is "SQL Server format (as SQL Data files)" ?

If you want a copy of the DB as a file that can be transferred easily then I
suggest a backup.

If you want the actual files used by the active DB then you will have to
detach the DB, copy the files then re-attach. Not a good idea as your DB
is offline whilst detached.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Aaron Longnion - Cosential" <aaron (AT) cosential (DOT) com> wrote

Sorry, by SQL Server format, I mean actual .mdf files.

But, remember I only want data for individual clients AND
I only need data from certain tables (about 200 of our 500
tables). Make sense? Basically, I need to be able to
dynamically create an .mdf file for any given client (by
ClientID), without giving them data that doesn't belong to
them.

I hope this explanation is better. Thanks,
Aaron

Sheesh, that doesn't sound like a very good approach at all.
What happens when 2 different clients request data at the same time. Do
they have to stand in a queue while the first request is validated, the
tables in the destination database are populated, the db detached, the mdf
files copied to an ftp site, the files ftp to the client, then it's their
turn?
Why not offer views of the data for that client through stored procedures,
so that only their data is returned in a web page, or within a report, or
some other usable fashion. Sending mdf files to ftp sites sounds like it
could turn into a nightmare pretty quickly.
What happens when someone logs into the ftp server, but hasn't made a
request. They can just grab what ever is there.

--
Simon Worth


"Aaron" <aaron (AT) cosential (DOT) com> wrote

Hi Simon -

Yes, I don't like the approach too much either, but the clients have
requested the data in a format that they can directly load into their SQL
Server databases.

1.) Remember, this is not direct access to the data, and we only will need
to do this every once in a while. The clients do not have the ability to
directly generate this data, and will only have access to it once the files
are ready.

a) they call or email us and ask for us to do this manually for them
b) we run some type of process (DTS, sproc, other?) that dynamically creates
a Client-specific DB
c) we copy the the MDF files to an FTP site (might do this programmatically
with ColdFusion MX)
d) give the client access only to their specific directory, and tell them
it's ready to download and where to go...

2.) this would be only a "one-at-a-time" thing, so we'd have manual control
so that there wouldn't have to be a queue or anything...

So, how would you approach this? DTS, sproc, or other method? Please give
details... ;-}

Thanks.
Aaron


"Simon Worth" wrote:

I wouldn't recommend using MDF files as you will receive warnings when
trying to attach that file if the transaction file is not also present.
Although, in this case, it doesn't seem that it will matter all that much.
Why not provide them with csv files of the data that can load directly into
the database with DTS or bcp.
You can send those files directly to the ftp site, and you won't have the
overhead of making sure the other database is empty, pushing records to the
other database, detaching the db, transferring the datafile to the ftp site,
the clients downloading the datafile, attaching the file (with warnings).
You could output the data to the ftp site in a flat file, they can load it
into their database using dts, and still preserving the history of what was
in their database before the new load.

--
Simon Worth


"Aaron Longnion" <Aaron Longnion (AT) discussions (DOT) microsoft.com> wrote

Using CSV files might be an option, or might not, depending on if our clients
accept it or not. But is there another option, such as making a backup (.BAK
files or whatever), and letting them download that and restoring as a new DB
on their side? Will that give warnings/errors on their SQL Server?

If I don't have a choice but to give them MDF or BAK files, what's the best
solution?

Thanks,
Aaron



"Simon Worth" wrote:

Any ideas anyone?

Thanks,
Aaron



"Aaron Longnion - hotmail" wrote:

If you use a backup, they won't receive the warnings associated to not
having a transaction log.
It still seems however that both ideas are a bit more extravagant (and more
difficult to administer) than they need to be.
Do your clients have/can be granted access to your database through stored
procs or views (of their own data). Or can you create a connection directly
to their SQL Server? If so, you could use DTS to push the data they need
directly to their database.
If they can't have access to stored procs to get relevant data, and you
cannot connect to their db server, I would suggest exporting to flat file.

--
Simon Worth


"Aaron Longnion - hotmail" <AaronLongnionhotmail (AT) discussions (DOT) microsoft.com>
wrote in message news:526865DA-B232-4F20-96A1-606B4816DC50 (AT) microsoft (DOT) com...

Thanks Simon!

I'll see what we can come up with, keeping your thoughtful suggestions in
mind.

Aaron



"Simon Worth" wrote: