apply table changes to diff application on a different server

apply table changes to diff application on a different server

Post by rick » Mon, 24 Mar 2003 06:49:03



How would I create a job that takes only the changes made
to Sybase Server tables and apply them to the same tables
on an MS SQL database.

Thx!

Rick

 
 
 

apply table changes to diff application on a different server

Post by Narayana Vyas Kondredd » Mon, 24 Mar 2003 18:11:31


Easiest would be to use replication. If your Sybase version supports
transactional replication to SQL Server subscribers, then all you need to do
is to setup a transactional publication on your Sybase table and subscribe
to that publication from SQL Server.

Another option would be to create triggers on your Sybase table, and update
the SQL Server tables using a linked server. As triggers fire synchronously,
this might impact the performance of data modification operations on your
Sybase table.

The above two suggestions need to be implemented on the Sybase server.

If you want SQL Server to do the work, then setup a linked server from SQL
Server to sybase. Then join your SQL Server tables with Sybase's to
determine the changes and apply them to the SQL Server tables. For example:

-- Pick the new rows from sybase table and
-- insert them into SQL Server

INSERT INTO SQLServerTable (Pkey, Col1, Col2)
SELECT Syb.Pkey, Syb.Col1, Syb.Col2
FROM SybaseServer.DbName.dbo.SybaseTable Syb
WHERE NOT EXISTS
(
    SELECT 1
    FROM SQLServerTable SQ
    WHERE Syb.Pkey = SQ.Pkey
)

You could do similar stuff for UPDATEs and DELETEs as well.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/


How would I create a job that takes only the changes made
to Sybase Server tables and apply them to the same tables
on an MS SQL database.

Thx!

Rick

 
 
 

apply table changes to diff application on a different server

Post by rick » Mon, 24 Mar 2003 18:59:14


Excellent!! Thanks! I will have to talk with the Sybase
DBA and work out my approach.

One question though, if I set up the Sybase triggers I
really only need to run a nightly job whereby the days
changes updates, deletes and inserts are applied to the
SQL Server database.  I don't want to drop and recreate
the tables, because some of them have millions of
records. How would I create a job to do this?

Thanks,
Rick

Quote:>-----Original Message-----
>Easiest would be to use replication. If your Sybase
version supports
>transactional replication to SQL Server subscribers,

then all you need to do
Quote:>is to setup a transactional publication on your Sybase
table and subscribe
>to that publication from SQL Server.

>Another option would be to create triggers on your

Sybase table, and update
Quote:>the SQL Server tables using a linked server. As triggers
fire synchronously,
>this might impact the performance of data modification
operations on your
>Sybase table.

>The above two suggestions need to be implemented on the
Sybase server.

>If you want SQL Server to do the work, then setup a

linked server from SQL
Quote:>Server to sybase. Then join your SQL Server tables with
Sybase's to
>determine the changes and apply them to the SQL Server

tables. For example:

>-- Pick the new rows from sybase table and
>-- insert them into SQL Server

>INSERT INTO SQLServerTable (Pkey, Col1, Col2)
>SELECT Syb.Pkey, Syb.Col1, Syb.Col2
>FROM SybaseServer.DbName.dbo.SybaseTable Syb
>WHERE NOT EXISTS
>(
>    SELECT 1
>    FROM SQLServerTable SQ
>    WHERE Syb.Pkey = SQ.Pkey
>)

>You could do similar stuff for UPDATEs and DELETEs as
well.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/



>How would I create a job that takes only the changes made
>to Sybase Server tables and apply them to the same tables
>on an MS SQL database.

>Thx!

>Rick

>.

 
 
 

apply table changes to diff application on a different server

Post by Narayana Vyas Kondredd » Mon, 24 Mar 2003 19:32:54


Okay, if you want only one update per day, then you cannot use triggers
directly. You need to accumulate changes and apply them all at once.
Replication is best suited for this. You could schedule the Sybases
distribution agent (don't exactly know what it is called in Sybase world) to
run once per day.

The last approach in my previous post would work as well. You'll have to
schedule those commands as a T-SQL job step in a job.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/


Excellent!! Thanks! I will have to talk with the Sybase
DBA and work out my approach.

One question though, if I set up the Sybase triggers I
really only need to run a nightly job whereby the days
changes updates, deletes and inserts are applied to the
SQL Server database.  I don't want to drop and recreate
the tables, because some of them have millions of
records. How would I create a job to do this?

Thanks,
Rick

Quote:>-----Original Message-----
>Easiest would be to use replication. If your Sybase
version supports
>transactional replication to SQL Server subscribers,

then all you need to do
Quote:>is to setup a transactional publication on your Sybase
table and subscribe
>to that publication from SQL Server.

>Another option would be to create triggers on your

Sybase table, and update
Quote:>the SQL Server tables using a linked server. As triggers
fire synchronously,
>this might impact the performance of data modification
operations on your
>Sybase table.

>The above two suggestions need to be implemented on the
Sybase server.

>If you want SQL Server to do the work, then setup a

linked server from SQL
Quote:>Server to sybase. Then join your SQL Server tables with
Sybase's to
>determine the changes and apply them to the SQL Server

tables. For example:

>-- Pick the new rows from sybase table and
>-- insert them into SQL Server

>INSERT INTO SQLServerTable (Pkey, Col1, Col2)
>SELECT Syb.Pkey, Syb.Col1, Syb.Col2
>FROM SybaseServer.DbName.dbo.SybaseTable Syb
>WHERE NOT EXISTS
>(
>    SELECT 1
>    FROM SQLServerTable SQ
>    WHERE Syb.Pkey = SQ.Pkey
>)

>You could do similar stuff for UPDATEs and DELETEs as
well.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/



>How would I create a job that takes only the changes made
>to Sybase Server tables and apply them to the same tables
>on an MS SQL database.

>Thx!

>Rick

>.