I tried to create a local snapshot replication from data A to database
B for sql 2005. I followed the wizard and it was created successfully.
But,nothing written to the replication folder and the job failed.
I manually executed the sqls and it always failed on
sp_addpublication_snapshot and the error was:
'DB4\Administrator' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
I log in to windows 2003 as administrator and the replication account
id dbsnap. What I have to do to avoid the error?
Is there a detailed step-by=step guide to create a snapshot
replication?
Can someone provide a set of sqls that I can just use to create a local
(or remote) snapshot?
Thanks,
Andy
The scripts are:
use [T2]
exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
@.value = N'true'
GO
-- Adding the snapshot publication
use [T2]
exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
publication of
database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
@.retention = 0,
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
N'true',
@.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot =
N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
= N'active',
@.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false',
@.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
@.allow_dts = N'false',
@.replicate_ddl = 1
GO
exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
1,
@.frequency_interval = 0, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 0, @.frequency_subday_interval = 0,
@.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.active_start_date = 0,
@.active_end_date = 0, @.job_login =
N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
@.publisher_login =
N'sa', @.publisher_password = N'chang5911'
use [T2]
exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
@.source_owner =
N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
@.description = null,
@.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x000000000803509D,
@.identityrangemanagementoption = N'manual', @.destination_table =
N'RETURN_REASON',
@.destination_owner = N'dbo', @.vertical_partition = N'false'
GO
Can you make the admin account( 'DB4\Administrator' ) part of the sysadmin
role on the publisher?
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
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1168789205.120731.163890@.51g2000cwl.googlegro ups.com...
>I tried to create a local snapshot replication from data A to database
> B for sql 2005. I followed the wizard and it was created successfully.
> But,nothing written to the replication folder and the job failed.
> I manually executed the sqls and it always failed on
> sp_addpublication_snapshot and the error was:
> 'DB4\Administrator' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
> I log in to windows 2003 as administrator and the replication account
> id dbsnap. What I have to do to avoid the error?
> Is there a detailed step-by=step guide to create a snapshot
> replication?
> Can someone provide a set of sqls that I can just use to create a local
> (or remote) snapshot?
> Thanks,
> Andy
>
> The scripts are:
> use [T2]
> exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> @.value = N'true'
> GO
> -- Adding the snapshot publication
> use [T2]
> exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> publication of
> database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> @.retention = 0,
> @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
> N'true',
> @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot =
> N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_subscription_copy = N'false',
> @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> = N'active',
> @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false',
> @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> @.allow_dts = N'false',
> @.replicate_ddl = 1
> GO
>
> exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
> 1,
> @.frequency_interval = 0, @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0,
> @.frequency_subday = 0, @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0,
> @.active_end_time_of_day = 235959, @.active_start_date = 0,
> @.active_end_date = 0, @.job_login =
> N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> @.publisher_login =
> N'sa', @.publisher_password = N'chang5911'
>
> use [T2]
> exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> @.source_owner =
> N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> @.description = null,
> @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x000000000803509D,
> @.identityrangemanagementoption = N'manual', @.destination_table =
> N'RETURN_REASON',
> @.destination_owner = N'dbo', @.vertical_partition = N'false'
> GO
>
Peter K wrote: