Updating Tables -- sql server replication

dario.dee NO[at]SPAM gmail.com
7/7/2006 4:23:42 AM

Hi,

I am trying to find a way to update tables on SQL Express. I have
replicated a database to a local database from a remote database using
linked servers but i want to be able to update the local database with
any changes from the remote database. That is i want to add new records
and update any records that have changed since the last update. At the
moment i am recreating the local database tables each time but it takes
really long, so i am hoping to find a way to update existing records
that have changed (that means i probably have to be able have to find
way of going through the two tables to check the primary and then check
if for that record if they are identical, if they are move on to next
record otherwise update the local record with what is in the remote
table record) Also check for any new records and append them to the
local table.

Is this possible from SQL Management Studio for SQL Express or do i
have to do it programatically. If it needs to be done programatically
can i please get some assistance with the source code to perform this,
preferably C# (I have been using a bit of Visual C# express 2005). I
have an ODBC connection to the remote database, which i use for the
linkserver.

I appreciate any help i can get.

Cheers,

dario.dee NO[at]SPAM gmail.com
7/7/2006 4:56:29 AM

I just have a read only ODBC connection to the remote database i can't
really do anything else but read the data from the tables.

[quoted text, click to view]

dario.dee NO[at]SPAM gmail.com
7/7/2006 7:24:57 AM

Thanks for the reply, but it seems like this is only for SQL Databases,
The remote database is an oracle database, can i still use DataCompare
for that?

[quoted text, click to view]

Paul Ibison
7/7/2006 1:58:05 PM

In that case I'd consider using Redgate's DataCompare and their API - you
can create a homegrown replication strategy quite easily this way.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Paul Ibison
7/7/2006 4:13:12 PM

Think this is SQL Server Specific. You might want to look at other 3rd party
tools for this eg DoubleTake
(http://www.cwlsystems.co.uk/double_take.html?WSCam=Google&WSEvt=Double-Take&gclid=COfjncjz_4UCFQWoEAodDjSJjQ).
SQL Server does allow for setting up of Oracle publishers, but as you are
using SQLExpress rather than Enterprise Edition this isn't going to be an
option.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

dario.dee NO[at]SPAM gmail.com
7/10/2006 1:57:47 AM

Okay thanks, but it is not really sql server specific, its just that i
have decided to use Sql Express (the ODBC connection is to an Oracle
database), because its free and quite easy to use, but unfortunately i
see it doesn't have the functionalities of Enterprise when it comes to
importing and updating tables so i am a bit stuck, my other option is
access, but i am not sure if i can count on it, because i am dealing
with thousands of records, the database is really big and its just
gonna get bigger.

I will have a look at the products you suggested.

Thanks again

If anyone has any ideas i would still appreciate them.

[quoted text, click to view]

Paul Ibison
7/21/2006 12:00:00 AM

Now I'm confused :) You've mentioned Oracle, SQLExpress and MySQL.
Presumably we are no longer talking about replication. I'd post this
question in the programming group.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

dario.dee NO[at]SPAM gmail.com
7/21/2006 12:49:50 AM

[quoted text, click to view]

Hi,

Well I've tried it out using Linked Servers but the remote server is a
MySql server, So I get this error now:
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for
linked server "remotedb". A four-part name was supplied, but the
provider does not expose the necessary interfaces to use a catalog or
schema.

The Query is as follows:

insert localdb.dbo.copytable
(
field1, field2, field3
)
select field1, field2, field3
from remotedb.dbname.dbo.tablename
where (not exists (
select *
from localdb.dbo.copytable
where (localdb.dbo.copytable.field1
= remotedb.dbname.dbo.tablename.field1)
))

I did manage to copy an entire table using openquery

select *
into localdb.dbo.copytable
from openquery( remotedb, 'select * from dbname.tablename' )

But for the insert and update i dont know how i can use the openquery,
any ideas?

for the update i was also hoping to use your query:

update dbo.LocalProductStatus
set dbo.LocalProductStatus.DateChanged
= dbo.RemoteProductStatus.DateChanged
,dbo.LocalProductStatus.ProductStatus
= dbo.RemoteProductStatus.ProductStatus
from dbo.LocalProductStatus
inner join dbo.RemoteProductStatus
on dbo.RemoteProductStatus.ProductName
= dbo.LocalProductStatus.ProductName
where (dbo.LocalProductStatus.DateChanged
!= dbo.RemoteProductStatus.DateChanged)
or (dbo.LocalProductStatus.ProductStatus
!= dbo.RemoteProductStatus.ProductStatus)

Any help will be greatly appreciated.

dario.dee NO[at]SPAM gmail.com
7/21/2006 2:02:25 AM

Sorry I posted this in the wrong place.

Cheers.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : Updating Tables