Stored Procedure to upload data from one db to remote db

[rockie12]

I have a situation where I want to take data from a local sql server
2000 db and update a remote database. I have the sql all set, was
wondering if this can be done in a timed interval with a stored
procedure on the local db.

Thanks in advance for your time
Dean-O

[Simon Hayes]

"rockie12" <ro******@dtnspeed.net> wrote in message
news:d1**************************@posting.google.c om...

I have a situation where I want to take data from a local sql server
2000 db and update a remote database. I have the sql all set, was
wondering if this can be done in a timed interval with a stored
procedure on the local db.

Thanks in advance for your time
Dean-O

It depends what data you want to copy. If you want to copy all changes from
the source database to the target, then you could look at log shipping or
replication. If you want to copy only a subset of data, then replication
would still be an option (you can replicate data conditionally), but you
might find it easier to write your own procedure.

Also, you need to bear in mind what the network connection is like. If both
servers are on the same LAN, then you could use linked servers to INSERT
directly from one to the other. But if you have a less reliable connection,
then you would probably want a different approach.

If this isn't helpful, you might want to give some more detail about exactly
what you need to do. You could also post in
microsoft.public.sqlserver.replication if you're interested in that
possibility.

Simon

[John Bell]

Hi

Simon has given you some alternatives, other alternatives may be DTS or
running the SQL (as a stored procedure). With SQL Server, if you wish to
schedule a timed event then usually SQLServerAgent is used to schedule
these. Agent jobs can be configured using Enterprise manager or using the
stored procedures sp_add_job, sp_add_jobstep and sp_add_jobschedule.

Each step can run a specific type of process (subsystem) such as 'CMDEXEC' -
an operating system command or executable. This could be used to run DTSRun
which can start a DTS job another alternative solution! 'TSQL' - a
transact-SQL statement (which can be a stored procedure).

John

"rockie12" <ro******@dtnspeed.net> wrote in message
news:d1**************************@posting.google.c om...

I have a situation where I want to take data from a local sql server
2000 db and update a remote database. I have the sql all set, was
wondering if this can be done in a timed interval with a stored
procedure on the local db.

Thanks in advance for your time
Dean-O

[rockie12]

More detail on the situation. The databases are one different
servers. I want to set up a situation where the remote database will
have a procedure that will run when the home base database is
available and update the home base database. Sort of a sync function.

I am new to sql server. I have done much with MySql and have taken
care of this type of function with a Java program that runs as a
windows event.

Thanks in advance
Dean-o

"John Bell" <jb************@hotmail.com> wrote in message news:<fw*********************@news-text.cableinet.net>...

Hi

Simon has given you some alternatives, other alternatives may be DTS or
running the SQL (as a stored procedure). With SQL Server, if you wish to
schedule a timed event then usually SQLServerAgent is used to schedule
these. Agent jobs can be configured using Enterprise manager or using the
stored procedures sp_add_job, sp_add_jobstep and sp_add_jobschedule.

Each step can run a specific type of process (subsystem) such as 'CMDEXEC' -
an operating system command or executable. This could be used to run DTSRun
which can start a DTS job another alternative solution! 'TSQL' - a
transact-SQL statement (which can be a stored procedure).

John

"rockie12" <ro******@dtnspeed.net> wrote in message
news:d1**************************@posting.google.c om...

I have a situation where I want to take data from a local sql server
2000 db and update a remote database. I have the sql all set, was
wondering if this can be done in a timed interval with a stored
procedure on the local db.

Thanks in advance for your time
Dean-O

[Erland Sommarskog]

rockie12 (ro******@dtnspeed.net) writes:

More detail on the situation. The databases are one different
servers. I want to set up a situation where the remote database will
have a procedure that will run when the home base database is
available and update the home base database. Sort of a sync function.

A simple-minded approach is to use linked servers:

UPDATE HOMESRV.database.dbo.tbl
SET col1 = local.col1,
col2 = local.col2,
...
FROM HOMESRV.database.dbo.tbl home
JOIN localtbl local ON home.keycol = local.keycol

You may need to add INSERT and DELETE as well.

You set up a linked server with sp_addlinkedserver.

I'm not really sure whether this is the best way. Maybe replication is
better. In that case, you don't have to bother about starting the
process once you get connected. Then again, replication takes some time
to set up.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp