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 login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?
In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change. A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:

> Is there a way to create a login and password, or change a login's password
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?
|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
[vbcol=seagreen]
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change. A
> dialog box will open and you can change the password in there, near the top.
> Type in the password in the Password box, and the same password again in the
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have a
password.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
[vbcol=seagreen]
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:

Create verticle grouping instead of horizontal

Hello,
Generally when using the Report Wizard with MS SQL 2005 it wants to present
the data horizontally, like this:
Item 1 Item2 Item3 Item4 Item5
Value1a Value2a Value3a Value4a Value5a
Value1b Value2b Value3b Value4b Value5b
... and so forth.
Instead of this, I need my report to present the data vertically, like this:
Item1 Value1a Value1b
Item2 Value2a Value2b
Item3 Value3a Value3b
Item4 Value4a Value4b
Item5 Value5a Value5b
I'm actually limiting this query to the Top 4 so it will only show columns
of data. Problem is, I have no idea how change the orientation of the
table. Can someone give me
some guidance on how to accomplish this? I'm using VB.Net 2003 to create
the report.
Thanks,
AlexOn Jul 26, 10:07 am, "Alex" <sama...@.gmail.com> wrote:
> Hello,
> Generally when using the Report Wizard with MS SQL 2005 it wants to present
> the data horizontally, like this:
> Item 1 Item2 Item3 Item4 Item5
> Value1a Value2a Value3a Value4a Value5a
> Value1b Value2b Value3b Value4b Value5b
> ... and so forth.
> Instead of this, I need my report to present the data vertically, like this:
> Item1 Value1a Value1b
> Item2 Value2a Value2b
> Item3 Value3a Value3b
> Item4 Value4a Value4b
> Item5 Value5a Value5b
> I'm actually limiting this query to the Top 4 so it will only show columns
> of data. Problem is, I have no idea how change the orientation of the
> table. Can someone give me
> some guidance on how to accomplish this? I'm using VB.Net 2003 to create
> the report.
> Thanks,
> Alex
You will want to look into using either a matrix report or pivoting
the data in the stored procedure/query that is souring the report.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant