Creating a Push Subscription on existing database.

My subscriber database has a subset of the tables in the Publisher but,
otherwise the schema is exactly the same.
Using the Push Subscription Wizard and the Initialize Subscription screen,
one is presented with two options: (I am using transactional publication)
-Yes, initialize the schema and data
-No, the Subscriber alreday has the schema and data
If I pick the first option, the initialization will fail because it tries to
drop the tables and views and my tables have relationships and contraints on
them.
If I pick the second option, the stored procedures used to update
(synchronize) the subcriber do not get created in the subcriber data base.
If I create a new database instead, everything works as expected.
How do I create a Push subscription where the table structure is already
there; but I do need to insure the stored procedures required by Replication
get created on the subscriber database?
Bill
William R
if you are running SQL 2k above sp1 do this in your publication database
sp_addpublication 'dummy'
sp_replicationdboption 'pubs','publish','true'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor','TableNameYouAr
ePublishingAndWantToGenerateAProcFor'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor2','TableNameYouA
rePublishingAndWantToGenerateAProcFor2'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor3','TableNameYouA
rePublishingAndWantToGenerateAProcFor3'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor4','TableNameYouA
rePublishingAndWantToGenerateAProcFor4'
sp_scriptpublicationcustomprocs 'dummy'
this will generate the procs you need in the results pane, script them out
and then issue a
sp_droppublication 'dummy'
"WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
news:D46CC819-EFD7-47ED-B39B-BDD090DE4E62@.microsoft.com...
> My subscriber database has a subset of the tables in the Publisher but,
> otherwise the schema is exactly the same.
> Using the Push Subscription Wizard and the Initialize Subscription screen,
> one is presented with two options: (I am using transactional publication)
> -Yes, initialize the schema and data
> -No, the Subscriber alreday has the schema and data
> If I pick the first option, the initialization will fail because it tries
to
> drop the tables and views and my tables have relationships and contraints
on
> them.
> If I pick the second option, the stored procedures used to update
> (synchronize) the subcriber do not get created in the subcriber data base.
> If I create a new database instead, everything works as expected.
> How do I create a Push subscription where the table structure is already
> there; but I do need to insure the stored procedures required by
Replication
> get created on the subscriber database?
> Bill
> --
> William R
|||Hilary,
I was wondering if something like this would be the solution. Since there
are so many tables, I could use the create database option to create a dummy
subscriber and copy the procedures over to the real subcriber.
It seems rather odd, MS didn't think of such an option for the wizard though.
Thank you for your response.
Bill
"Hilary Cotter" wrote:

> if you are running SQL 2k above sp1 do this in your publication database
> sp_addpublication 'dummy'
> sp_replicationdboption 'pubs','publish','true'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor','TableNameYouAr
> ePublishingAndWantToGenerateAProcFor'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor2','TableNameYouA
> rePublishingAndWantToGenerateAProcFor2'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor3','TableNameYouA
> rePublishingAndWantToGenerateAProcFor3'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor4','TableNameYouA
> rePublishingAndWantToGenerateAProcFor4'
> sp_scriptpublicationcustomprocs 'dummy'
> this will generate the procs you need in the results pane, script them out
> and then issue a
> sp_droppublication 'dummy'
>
> "WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
> news:D46CC819-EFD7-47ED-B39B-BDD090DE4E62@.microsoft.com...
> to
> on
> Replication
>
>
|||That is another way, but it is more work.
I would advise you however to script out the publishing database, create a
database called pub, and a database called sub.
In pub, run the creation script. Then run your publication script (changing
the publication name), and then create and push your subscription to sub.
This way your snapshot generation time will be very very fast and the impact
on your publisher will be low.
"WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
news:AB0769C1-646E-4406-8B50-68A60BE62109@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I was wondering if something like this would be the solution. Since there
> are so many tables, I could use the create database option to create a
> dummy
> subscriber and copy the procedures over to the real subcriber.
> It seems rather odd, MS didn't think of such an option for the wizard
> though.
> Thank you for your response.
> Bill
>
> "Hilary Cotter" wrote:

creating a new replication

I am very new to the whole sql replication, i am trying to replicate with 2x 2000sql std machines

One is to be the distrubuter and the publisher (merge type by the way) the other to be the subscriber.

I have downloaded and followed step by steps religiously however i have nothing but isses with this. I have used the wizards i am getting various errors.

the merge client comes up with an error after looking in the event view i find i have the following error

Event Type: Information
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 203
Date: 8/01/2007
Time: 12:52:17 PM
User: N/A
Computer: FAILOVERSQL2000
Description:
SubSystem Message - Job 'FAILOVERSQL2000-PTO-PTO-ELCOM-TEST-1' (0x96AD1DBC3E5A9541A6C72C4CCFBF5AED), step 2 - The schema script '\\FAILOVERSQL2000\ReplData\unc\FAILOVERSQL2000_PTO_PTO0108120754\forums_AddUserToRole_5110.sch' could not be propagated to the subscriber.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

What can i do to get this fixed?

Thanks

From the subscriber, can you access that file using that particular UNC path - \FAILOVERSQL2000\ReplData\unc\FAILOVERSQL2000_PTO_PTO0108120754\forums_AddUserToRole_5110.sch. If not, then your share is not set up properly.

However you should be able to use replication monitor to get more detailed information. Open replication monitor, add your publisher, then drill down into failed error message where you should have more detailed history on why it cannot be propogated to the subscriber. If you still need help, please post back here.