sql server dts: sp over 2 differente DB -- sql server dts

sp over 2 differente DB

Bruno Alexandre
1/28/2004 4:58:47 PM

sql server dts: Hi guys,

I wonder How can I build a Store Procedure with two connections?

What I need is to develop a SP to backup the SQL DB that's on Internet
(Hosting Company, and they do not replicate it, well, not without any more
money).
DTS does not have any tool to do that, just ADD, DELETE & INSERT, etc...
cause it's too many records and too many tables, now, it takes 3 hours to
insert all the SQL Database from the Hosting Company to my local Computer
(192Kbps dedicate line, here in the company).

I need to build a SP that reads the last ID that I have in my local PC
and insert from the Hosting company SQL all records > ID that I have.

How can I do this in a SP? How can I manage to work with 2 different
connections?

any ideas, tutorials, anything? All help is appreciate, thank you in
advance.

--

Bruno Miguel Alexandre
Dep Informática do Grupo Filtrarte

Av General Humberto Delgado, 91
Vila Verde
2705-887 Terrugem SNT
Portugal

T. +351 219 608 130
F. +351 219 615 369
w. www.filtrarte.com
@. bruno@filtrarte.com

Re: sp over 2 differente DB

Allan Mitchell
1/28/2004 5:21:22 PM

OK

You actually want an incremental update of your local box from the ISPs.

I would place triggers on the tabes I need and log the changes (UPDATE,
INSERT, DELETE)

I would then have a package with a series of DDQ tasks that read the table
and apply those changes to your local DB.
Another way would be to implement a very crude version of merge replication.
You check an attribute value on the source against the destination and take
action from there.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

[quoted text, click to view]