Transactional Replication from SQL2K5 to Sql 7.0 (sp4)

Question

Transactional Replication from SQL2K5 to Sql 7.0 (sp4)

Pankaj Shere

SSC Eights!

Points: 907
More actions
May 7, 2008 at 9:40 am

#207118

I am trying to replicate some tables from SQL2k5 (SP2) to SQL (7.0). My publisher and distributor is the 2K5 box and I want the 7.0 to be the subscriber. According to this article on microsoft.com http://msdn.microsoft.com/en-us/library/ms143241.aspx I should be able to do it. But when I try to add the 7.0 as a subscriber, I get an error which says "This SQL Server version (7.0) is not supported"
Is there a workaround to make it work. Or can I manually run the stored procs and set the 7.0 box to be the subscriber.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

thasteve SSCrazy Points: 2707 More actions · Answer 1

Are trying to set it up in the SSMS of SQL 2005? This would fail with the error you have to use the stored procedures in SQL 7 to set it up as subscriber.

Pankaj Shere SSC Eights! Points: 907 More actions · Answer 2

Thanks. Where can I find the stored procs to setup the replication on the subscriber running on sql 7.0

dbaker-620086 SSCrazy Points: 2336 More actions · Answer 3

presumably you can setup the publication on the Publisher (SQL2005) and Distributor (SQL2005).

- you can script this to produce the sproc calls to drop+create that pub

in such scripts you will find TSQL like

sp_addpublication ...

, @allow_pull = 'true'

, @pre_snapshot_script= 'pre_snapshot_script'

, @post_snapshot_script= 'post_snapshot_script'

, @allow_subscription_copy = 'allow_subscription_copy'

, @replicate_ddl = 'false'

so you should be able to use SEM on SQL7 to create the sub ?

if that fails, I suggest you use SSMS to create a sub to DUMMY (i.e. some valid SQL2005 instance like your sandbox), re-script to give pub+subs, edit script from DUMMY to SS7SUB, execute script (on SQL2005).

I have found problems with flowing indexes from pub to sub (see the sp_addarticle @schema_option=0x40) as it tries to use INCLUDE clause (as can be seen using tracing/Profiler) which won't work on pre-SS2K5 subs, so you will probably have to bury such frills in the 'post_snapshot_script'

let us know how you get on !

BTW SS7 is no longer supported so strongly recommended that you upgrade

- even to SQL2005\SQLEXPRESS perhaps as a no-cost option!

HTH

Dick