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.

nocheck when creating a constraint

i was looking at a database creation script and i have a bunch of
constraints being created with a nocheck on them. i think i know what
the nocheck does (don't check for data issues). i'm not sure why the
original coder would define them like this since there is no data to
begin with when the tables are built. am i missing something? does
this hurt the database table?
ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]That seems to be a stupid thing to do. You won't gain anything, and possibly just have a non-trusted
constraint which can lead to worse performance (non-trusted will limit some of the optimizations
that can be done).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199834.825140.114810@.y5g2000hsa.googlegroups.com...
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]
>|||Derek,
See this blog from SQL Server MVP Hugo Kornelis.
Can you trust your constraints?
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
AMB
"Derek" wrote:
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]
>

nocheck when creating a constraint

i was looking at a database creation script and i have a bunch of
constraints being created with a nocheck on them. i think i know what
the nocheck does (don't check for data issues). i'm not sure why the
original coder would define them like this since there is no data to
begin with when the tables are built. am i missing something? does
this hurt the database table?
ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMAR
Y]That seems to be a stupid thing to do. You won't gain anything, and possibly
just have a non-trusted
constraint which can lead to worse performance (non-trusted will limit some
of the optimizations
that can be done).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199834.825140.114810@.y5g2000hsa.googlegroups.com...
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [P
RIMARY]
>|||Derek,
See this blog from SQL Server MVP Hugo Kornelis.
Can you trust your constraints?
http://sqlblog.com/blogs/hugo_korne.../>
raints.aspx
AMB
"Derek" wrote:

> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [
PRIMARY]
>