Best approach to updating remote server?

I have a local production server, and a web server at a remote ISP connected
via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
of data on the production server and update it to the web server daily. The
web server only gets a subset of the information on the production server.
I know I can't be the first to need this so what I am searching for is the
best way to accomplish this. Do I refresh the web server daily (about 500MB
of data)? Should I send each table with an indicator as to
Add/Change/Delete, and write a procedure to import? or is there just another
solution I am overlooking.
Any suggestions would be appreciated.
TIA, MarkHi Mark.
A common approach to achieving this is to create a DTS package that
encapsulates the process, applies business rules to the data etc & then
schedule it to run from the local production server. I've done this with
various ISP hosted SQL databases with fairly good success.
Is your ISP hosted SQL Server dedicated or shared? If dedicated, many
options may open up to you that a shared server might have available.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:enU%238d8DEHA.2932@.tk2msftngp13.phx.gbl...
> I have a local production server, and a web server at a remote ISP
connected
> via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
> of data on the production server and update it to the web server daily.
The
> web server only gets a subset of the information on the production server.
> I know I can't be the first to need this so what I am searching for is the
> best way to accomplish this. Do I refresh the web server daily (about
500MB
> of data)? Should I send each table with an indicator as to
> Add/Change/Delete, and write a procedure to import? or is there just
another
> solution I am overlooking.
> Any suggestions would be appreciated.
> TIA, Mark
>

Before Update/Delete Trigger

Is there a way to create a trigger that will keep a user from updating or deleting a record? Thanks, JeremyRead about INSTEAD OF triggers in BOL|||Hi JCScoobyRS,

Originally posted by JCScoobyRS
Is there a way to create a trigger that will keep a user from updating or deleting a record? Thanks, Jeremy

h, why don't you revoke the user the UPDATE and DELETE permission?|||Very good idea BUT I'm trying to help a buddy out that needed the ability described in my first post. Is there a way? I'll check with him to see if that will work but I wouldn't mind an answer anyways. Thanks for your help, Jeremy|||Originally posted by JCScoobyRS
Very good idea BUT I'm trying to help a buddy out that needed the ability described in my first post. Is there a way? I'll check with him to see if that will work but I wouldn't mind an answer anyways. Thanks for your help, Jeremy

In this case you should go for INSTEAD OF triggers|||Okay...that sounds good. Here is an example of what I need to do:

I'm trying to prevent the UPDATE and DELETE on a table after a certain field has been entered(not null). Here is the trigger right now:

CREATE TRIGGER TRANSACTION_REPORTEE ON [dbo].[TRAVAUX_COMMANDE]
FOR UPDATE, DELETE
AS
IF [dbo].[TRAVAUX_COMMANDE].[Id_Transaction_GL] IS NOT NULL
BEGIN
RAISERROR ('Impossible de modifier une ligne reporte',10,1)
ROLLBACK TRAN
END

This is what my buddy has. Is there anyway to take what he has here and revise it with your idea in it for testing? Thanks alot, Jeremy