sql 2005 to Sybase?
Great...
I need a live feed of inserts updates and deletes from a MS SQL 2005 table
to a Sybase table, do you have a general idea how I can accomplish this with
SSIS?
Thx!
"Paul Ibison" wrote:
> As far as I know, only Oracle and DB2 are supported
> (http://technet.microsoft.com/en-us/library/ms151864(SQL.90).aspx). Could
> use SSIS and factor your own solution instead.
> HTH,
> Paul Ibison
>
>
|||I would not use ssis for this. You can create a subscription in SQL Server,
and then use sp_browsereplcmds to get the stored procedure calls and then
run them on the sybase server. You can pipe the results of sp_browsereplcmds
to a results table and select the tranaction IDs you want to stop and start
at.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:44200793-FAA0-4F75-93F5-AD3671FBFD13@.microsoft.com...[vbcol=seagreen]
> Great...
> I need a live feed of inserts updates and deletes from a MS SQL 2005 table
> to a Sybase table, do you have a general idea how I can accomplish this
> with
> SSIS?
> Thx!
> "Paul Ibison" wrote:
|||So this will give allow me to perform transactional replication to a Sybase
server table?
"Hilary Cotter" wrote:
> I would not use ssis for this. You can create a subscription in SQL Server,
> and then use sp_browsereplcmds to get the stored procedure calls and then
> run them on the sybase server. You can pipe the results of sp_browsereplcmds
> to a results table and select the tranaction IDs you want to stop and start
> at.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:44200793-FAA0-4F75-93F5-AD3671FBFD13@.microsoft.com...
>
>
|||Sort of. If you capture these statements they can be run on a sybase server.
It is possible to programmatically do this.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:B54DE9D7-9E2A-48D1-A843-492AF75A40C9@.microsoft.com...[vbcol=seagreen]
> So this will give allow me to perform transactional replication to a
> Sybase
> server table?
>
> "Hilary Cotter" wrote:
|||Can you provide a bit more detail of how I could accomplish this
programmatically.
"Hilary Cotter" wrote:
> Sort of. If you capture these statements they can be run on a sybase server.
> It is possible to programmatically do this.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:B54DE9D7-9E2A-48D1-A843-492AF75A40C9@.microsoft.com...
>
>
|||here is an example:
--create the table status
--create table status(pk int not null identity primary key, minxact_seqno
varbinary(16), maxxact_seqno varbinary(16))
declare @.maxxact_seqno varbinary(16)
declare @.minxact_seqno varbinary(16)
select @.minxact_seqno =maxxact_seqno from status
if @.@.rowcount=0
select @.minxact_seqno =xact_seqno From msrepl_transactions order by
entry_time
select @.maxxact_seqno =xact_seqno From msrepl_transactions order by
entry_time desc
print @.minxact_seqno
print @.maxxact_seqno
exec sp_browsereplcmds @.minxact_seqno ,@.maxxact_seqno , @.results_table
='MyComamnds'
insert into status (maxxact_seqno, minxact_seqno ) values(@.maxxact_seqno,
@.minxact_seqno )
go
--now what you do is read through the table MyCommands and apply these
commands on Sybase, you can use ado or a linked server for this
--when you have read everything drop the table MyCommands and repeat this
job.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1E733A3A-B91F-492D-920E-52D414A89E80@.microsoft.com...[vbcol=seagreen]
> Can you provide a bit more detail of how I could accomplish this
> programmatically.
> "Hilary Cotter" wrote:
|||I'm back...with more questions
1st
I'm thinking that:
@.minxact_seqno ,@.maxxact_seqno
should read:
@.xact_seqno_start, @.xact_seqno_end
yes/no?
2nd
so it won't really be transactional replication, it'll be a scheduled job?
3rd
how will the distribution database get cleared of it's transactions, once
they've been applied to the sybase server. what do i use as a subscriber?
4th
I'm familiar with setting up linked servers,
what's the best way to read through the table and apply the transactions?
BTW, Thx a mill for your time and help!
"Hilary Cotter" wrote:
> here is an example:
> --create the table status
> --create table status(pk int not null identity primary key, minxact_seqno
> varbinary(16), maxxact_seqno varbinary(16))
> declare @.maxxact_seqno varbinary(16)
> declare @.minxact_seqno varbinary(16)
> select @.minxact_seqno =maxxact_seqno from status
> if @.@.rowcount=0
> select @.minxact_seqno =xact_seqno From msrepl_transactions order by
> entry_time
> select @.maxxact_seqno =xact_seqno From msrepl_transactions order by
> entry_time desc
> print @.minxact_seqno
> print @.maxxact_seqno
> exec sp_browsereplcmds @.minxact_seqno ,@.maxxact_seqno , @.results_table
> ='MyComamnds'
> insert into status (maxxact_seqno, minxact_seqno ) values(@.maxxact_seqno,
> @.minxact_seqno )
> go
> --now what you do is read through the table MyCommands and apply these
> commands on Sybase, you can use ado or a linked server for this
> --when you have read everything drop the table MyCommands and repeat this
> job.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1E733A3A-B91F-492D-920E-52D414A89E80@.microsoft.com...
>
>
|||Answers inline.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A3B3337C-DEDD-4811-97D9-DBC1A4051684@.microsoft.com...
> I'm back...with more questions
> 1st
> I'm thinking that:
> @.minxact_seqno ,@.maxxact_seqno
> should read:
> @.xact_seqno_start, @.xact_seqno_end
> yes/no?
>
OK, but the naming convention I use implies the maxiumn one last collected
and the current maxiumn. Its all a naming convention - whatever works for
you
> 2nd
> so it won't really be transactional replication, it'll be a scheduled job?
It will be transactional replication to a dummy subscriber. You will need to
create some scheduled jobs to read the commands and then replay them on
Sybase.
> 3rd
> how will the distribution database get cleared of it's transactions, once
> they've been applied to the sybase server. what do i use as a subscriber?
Create a dummy database - a plain vanilla one on the publisher and
replicated there. Delete rows from it on a schedule to keep it small and
then set your min retention to 6 hours or so, a time interval which will
allow you to collect all the commands. The distribution clean up job will
remove them from the distribution database. But it doesn't know if they have
been applied on Sybase yet, so you will have to make sure you collect them
before they are deleted.
> 4th
> I'm familiar with setting up linked servers,
> what's the best way to read through the table and apply the transactions?
You will have to implement some sort of tracking column. Perhaps an tinyint
or int col with a default of 0, as you apply the command delete it from the
table. You can also create tables for each day or hour and then read them
until all the commands are applied and then drop them.
> BTW, Thx a mill for your time and help!
Your welcome
[vbcol=seagreen]> "Hilary Cotter" wrote:
|||Thanks for the comments, the picture is much clearer now.
I've made some progress.
1. I've installed the Sybase drivers
2. I've setup the oledb connection and successfully retrieved data from the
Sybase tables using the linked server.
I've now hit a snag and need your expertise.
The parameters for sp_browsereplcmds are no longer the same.
The @.results_table is no longer a parameter.
What's the alternative in 2005 to accomplish the same thing?
"Hilary Cotter" wrote:
> Answers inline.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A3B3337C-DEDD-4811-97D9-DBC1A4051684@.microsoft.com...
> OK, but the naming convention I use implies the maxiumn one last collected
> and the current maxiumn. Its all a naming convention - whatever works for
> you
> It will be transactional replication to a dummy subscriber. You will need to
> create some scheduled jobs to read the commands and then replay them on
> Sybase.
> Create a dummy database - a plain vanilla one on the publisher and
> replicated there. Delete rows from it on a schedule to keep it small and
> then set your min retention to 6 hours or so, a time interval which will
> allow you to collect all the commands. The distribution clean up job will
> remove them from the distribution database. But it doesn't know if they have
> been applied on Sybase yet, so you will have to make sure you collect them
> before they are deleted.
> You will have to implement some sort of tracking column. Perhaps an tinyint
> or int col with a default of 0, as you apply the command delete it from the
> table. You can also create tables for each day or hour and then read them
> until all the commands are applied and then drop them.
> Your welcome
>
>
No comments:
Post a Comment