[Help] Snapshot replication & Full Text Indexing

Hi
I have setup snapshot replication in one of my sql database. The article
which i publish, has full text index enabled in the subscription db. So when
the initial snap shot is applied, i get a error message saying "cannot drop
index PK_<<table name>> because it enforces full text key. It really drives
me crazy.
Here are my questions
1. In the Artcile properties, page i specify, under the name conflict
section, leave the table un changed. Then why is it that sql server tries
to drop the table and recreate it.(i checked in the sch table, what ever
option i specify, it is adament, it wants to recreate the table).
2. Is there a way, to publish full text indexed tables, without the initial
snapshot failing ?
I am in "Developer in Distress" mode, can anyone help ?
Thank You
Shrinivas
1 & 2) this should work, could you post the schema of this problem table
here?
"shrini" <senor20@.hotmail.com> wrote in message
news:%23R4xv8vJEHA.620@.tk2msftngp13.phx.gbl...
> Hi
> I have setup snapshot replication in one of my sql database. The article
> which i publish, has full text index enabled in the subscription db. So
when
> the initial snap shot is applied, i get a error message saying "cannot
drop
> index PK_<<table name>> because it enforces full text key. It really
drives
> me crazy.
> Here are my questions
> 1. In the Artcile properties, page i specify, under the name conflict
> section, leave the table un changed. Then why is it that sql server tries
> to drop the table and recreate it.(i checked in the sch table, what ever
> option i specify, it is adament, it wants to recreate the table).
> 2. Is there a way, to publish full text indexed tables, without the
initial
> snapshot failing ?
> I am in "Developer in Distress" mode, can anyone help ?
> Thank You
> Shrinivas
>
|||Hi Hilary
I managed to work around this, by dropping the Full Text index before
applying the snap (by specifying sql in the snap shot tab of properties) and
recreating the index after the snap shot is created. I think this is the
possible way for doing this. Thanks for your reply.
Shrini
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:e3KduPFKEHA.2808@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> 1 & 2) this should work, could you post the schema of this problem table
> here?
> "shrini" <senor20@.hotmail.com> wrote in message
> news:%23R4xv8vJEHA.620@.tk2msftngp13.phx.gbl...
> when
> drop
> drives
tries
> initial
>

@force_reinit_subscription

Hi,

If I add or remove a merge article from a publication and I specify the @.force_reinit_subscription = 1, will this force a new snapshot to be sent to all subscribers? If it does, will it apply the who snapshot as it did when the subscription was created or is it clever enough to work out what bits are different?

Thanks for your help

Graham

if you drop an article, and you force reinit and snapshot generation, depending on exactly what needs to be generated, you may be required to create a new snapshot (and dynamic snapshot), and apply new snapshot. i.e. if you drop a static article (not filtered, nor not participating in any joins), you may not need to generate a snapshot. But if you the article is a filtered article, or participates in a join, the snapshot may need to be reapplied.

So yes, it's clever :)

@bitmap was not supplied to upd procedure in transactional

Initially when I had setup my transactional replication I had created it with
out taking a snapshot and by synching the databases.
Now I am trying to add new table to one of my existing publication in
production by creating scripts for ins, upd and del in test server and
deploying it to subscriber but it throws an error saying
"sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
Inserts just work fine.
This is what i used to add-
exec sp_addarticle @.publication = 'Repl_Source'
, @.article = TableName
, @.source_table = TableName
, @.sync_object = null
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
how many columns are in your table?
Hilary Cotter
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
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:AF5F5FE4-E43C-48C6-8FF9-9739C7C3DE62@.microsoft.com...
> Initially when I had setup my transactional replication I had created it
> with
> out taking a snapshot and by synching the databases.
> Now I am trying to add new table to one of my existing publication in
> production by creating scripts for ins, upd and del in test server and
> deploying it to subscriber but it throws an error saying
> "sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
> Inserts just work fine.
> This is what i used to add-
> exec sp_addarticle @.publication = 'Repl_Source'
> , @.article = TableName
> , @.source_table = TableName
> , @.sync_object = null
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
>
>
|||18 columns.
If I rememebr correctly I have tested same before a 6 months agao on
different table and i rememeber it working. Not sure what changed.
"Hilary Cotter" wrote:

> how many columns are in your table?
> --
> Hilary Cotter
> 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
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:AF5F5FE4-E43C-48C6-8FF9-9739C7C3DE62@.microsoft.com...
>
>
|||Hi Hilary,
I think this is what thats happenening
- As I have scripted ins, upd and del scripts in dev environment by
generating them by running snapshot in transactional replication, those are
scripted with @.bitmap varaiable.
- Looks like the code I have used to add subscription
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
is trying for ins, upd and del procs with no @.bitmap.
Instead of above code I have used
exec sp_addsubscription
@.publication = Repl_Source,
@.article = TableName,
@.subscriber = SERVERNAME,
@.destination_db = 'Repl_Destination',
@.sync_type = N'automatic',
@.subscription_type = N'pull',
@.update_mode = N'read only'
then when i made data changes it asked for snapshot to be execute dfirst and
it created sp's at destination with out bitmap
As all my other sp's were created during initial setup with bitmap i would
like them to be in sync . rather than some with bitmap variable and some with
out..
Any advise....
"SQL Replication Guy" wrote:
[vbcol=seagreen]
> 18 columns.
>
> If I rememebr correctly I have tested same before a 6 months agao on
> different table and i rememeber it working. Not sure what changed.
> "Hilary Cotter" wrote:
|||return to your publisher and in qa change to the publication database, then
recreate your replication procs by issuing a sp_scriptpublicationcustomprocs
'publicationName'
then copy what you find in the results pane, go to the subscriber, change to
the subscription database, and paste these results into qa and run them.
Hilary Cotter
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
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:C261558E-58A6-4810-A5E1-AB24439B9A9E@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> I think this is what thats happenening
> - As I have scripted ins, upd and del scripts in dev environment by
> generating them by running snapshot in transactional replication, those
> are
> scripted with @.bitmap varaiable.
> - Looks like the code I have used to add subscription
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
> is trying for ins, upd and del procs with no @.bitmap.
>
> Instead of above code I have used
> exec sp_addsubscription
> @.publication = Repl_Source,
> @.article = TableName,
> @.subscriber = SERVERNAME,
> @.destination_db = 'Repl_Destination',
> @.sync_type = N'automatic',
> @.subscription_type = N'pull',
> @.update_mode = N'read only'
> then when i made data changes it asked for snapshot to be execute dfirst
> and
> it created sp's at destination with out bitmap
>
> As all my other sp's were created during initial setup with bitmap i would
> like them to be in sync . rather than some with bitmap variable and some
> with
> out..
> Any advise....
>
> "SQL Replication Guy" wrote:
|||Hi Hilary,
I was trying several options soory could get to try your option but this
worked--
sp_addarticle
@.publication = 'Repl_Source',
@.article = 'TableName',
@.source_object = 'TableName',
@.destination_table = 'TableName',
@.ins_cmd = 'call sp_MSins_TableName,
@.del_cmd = 'call sp_MSdel_TableName',
@.upd_cmd = 'mcall sp_MSupd_TableName', -- MCALL CREATED bitmap in UPD sp
@.type = 'logbased',
@.pre_creation_cmd = 'drop',
@.status = 16,
@.source_owner = 'dbo',
@.vertical_partition = 'false',
@.filter = Null,
@.auto_identity_range = 'false'
and then
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
"SQL Replication Guy" wrote:

> Initially when I had setup my transactional replication I had created it with
> out taking a snapshot and by synching the databases.
> Now I am trying to add new table to one of my existing publication in
> production by creating scripts for ins, upd and del in test server and
> deploying it to subscriber but it throws an error saying
> "sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
> Inserts just work fine.
> This is what i used to add-
> exec sp_addarticle @.publication = 'Repl_Source'
> , @.article = TableName
> , @.source_table = TableName
> , @.sync_object = null
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
>
>