Nomore snapshot without locking tables?

HI There

After upgrading my publishers to 2005 i noticed that i cannot specify not to lock tables during snapshot during publication creation, also not on publication properties, and i see sp_addpublication has no such parameter, is there no longer an option not to lock publication tables during snapshot?

Thanx

Hi Dietz,

You can specify the @.sync_method parameter of sp_addpublication to be concurrent or concurrent_c.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl_4s32.asp

[ @.sync_method=] 'sync_method'

Is the synchronization mode. sync_method is nvarchar(13), and can be one of the following values.

Value

Description

native

Produces native-mode bulk copy program output of all tables. Not supported for Oracle Publishers.

character

Produces character-mode bulk copy program output of all tables. For an Oracle Publisher, character is valid only for snapshot replication.

concurrent

Produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot. Only supported for transactional publications. Not supported for Oracle Publishers.

concurrent_c

Produces character-mode bulk copy program output of all tables but does not lock tables during the snapshot. Only supported for transactional publications.

NULL (default)

Defaults to native for Microsoft SQL Server Publishers. For non-SQL Server Publishers, defaults to character when the value of repl_freq is Snapshot and to concurrent_c for all other cases.

Regards,

Gary

|||

Thanx Gary

As far as i can see this option is not available though management studio when creating a publication or viewing publication properties after creation, correct ? Only through TSQL.

No tables in merge replication

Hi, I have setup a publication that my program on my CE device will
synchronize to. It connects and downloads the database fine, and the database
file shows that data has been downloaded. But when I try to run SQL
statements against the database, it says that the tables are not there. I
also used the Query Analyzer that is installed on the machine when using
vs.net, and it does not show any tables, either. I was wondering if there was
something that I was doing wrong when setting up the publication.
Thanks
Daniel Robinson
This is highly abnormal. Can you possibly post your code here?
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
"Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> Hi, I have setup a publication that my program on my CE device will
> synchronize to. It connects and downloads the database fine, and the
database
> file shows that data has been downloaded. But when I try to run SQL
> statements against the database, it says that the tables are not there. I
> also used the Query Analyzer that is installed on the machine when using
> vs.net, and it does not show any tables, either. I was wondering if there
was
> something that I was doing wrong when setting up the publication.
> Thanks
> Daniel Robinson
|||I don't think it's a problem with my code. We had a publication setup for
another database, and that was working. All I did in the code was change the
publication name.
Daniel
"Hilary Cotter" wrote:

> This is highly abnormal. Can you possibly post your code here?
> --
> 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
> "Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
> message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> database
> was
>
>
|||Here is the SQL that I used to create the publication:
EXEC sp_replicationdboption @.dbname='daniel', @.optname='merge publish',
@.value='true'
EXEC sp_addmergepublication @.publication='uadd_AMSSchool_Daniel',
@.allow_pull='true', @.allow_push='false',
@.allow_anonymous='true', @.enabled_for_internet='true',
@.snapshot_in_defaultfolder='false', @.alt_snapshot_folder='C:\SqlPublication',
@.compress_snapshot='false', @.sync_mode='character', @.retention=60,
@.conflict_retention=60, @.allow_synctoalternate='true',
@.keep_partition_changes='true'
EXEC sp_addpublication_snapshot @.publication='uadd_AMSSchool_Daniel',
@.frequency_type=8, @.frequency_interval=1,
@.frequency_subday=1, @.frequency_subday_interval=1,
@.frequency_recurrence_factor=1
EXEC sp_grant_publication_access @.publication='uadd_AMSSchool_Daniel',
@.login='sa'
EXEC sp_addmergearticle @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.source_object='mytable', @.source_owner='dbo',
@.type='table', @.destination_owner='dbo', @.column_tracking='true',
@.force_invalidate_snapshot=1, @.vertical_partition='true'
EXEC sp_mergearticlecolumn @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.column='myname', @.operation='add',
@.schema_replication='true', @.force_invalidate_snapshot=1,
@.force_reinit_subscription=1
EXEC sp_mergearticlecolumn @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.column='myint', @.operation='add',
@.schema_replication='true', @.force_invalidate_snapshot=1,
@.force_reinit_subscription=1
I do get a warning that only Subscribers running SQL Server 2000 can
synchronize with the publication...and I am running sqlCE. You think this
might be causing a problem?
Thanks
Daniel
"Hilary Cotter" wrote:

> This is highly abnormal. Can you possibly post your code here?
> --
> 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
> "Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
> message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> database
> was
>
>