June 23, 2009 at 12:22 pm
[font="Arial Black"]I'm attempting a fresh setup of a Distributor on a SQL 2005 instance.
I'm executing this code:[/font]
[font="Courier New"]use master
exec sp_adddistributor @distributor = N'Server1\Instance1', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'h:', @log_folder = N'h:', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO[/font]
[font="Arial Black"]Here's the message log returned:[/font]
[font="Courier New"]Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Creating distribution tables
Creating table MSrepl_version
Creating table MSpublisher_databases
Creating clustered index ucMSpublisher_databases
Creating table MSpublications
Creating clustered index ucMSpublications
Creating table MSarticles
Creating clustered index ucMSarticles
Creating table MSsubscriptions
Creating clustered index ucMSsubscirptions
Creating index iMSsubscriptions
Creating index iMSsubscriptions2
Creating table MSmerge_subscriptions
Creating clustered index ucMSmerge_subscriptions
Creating table MSrepl_transactions
Creating clustered index usMSrepl_transactions
Creating table MSrepl_commands
Creating clusterd index ucMSrepl_commands
Creating table MSrepl_orginators
Creating clustered index usMSrepl_originators
Creating table MSsubscriber_info
Creating clustered index ucMSsubscriber_info
Creating table MSsubscriber_schedule
Creating table MSsnapshot_history
Creating clustered index ucMSsnapshot_history
Creating table MSlogreader_history
Creating clustered index ucMSlogreader_history
Creating table MSdistribution_history
Creating clustered index ucMSdistribution_history
Creating table MSsnapshot_agents
Creating clustered index ucMSsnapshot_agents
Creatingindex iMSsnapshot_agents
Creating table MSlogreader_agents
Creating clustered index ucMSlogreader_agents
Creatingindex iMSlogreader_agents
Creating table MSdistribution_agents
Creating clustered index ucMSdistribution_agents
Creatingindex iMSdistribution_agents
Creating table MSmerge_agents
Creating clustered index ucMSmerge_agents
Creating table MSrepl_identity_range
Creating table MSpublication_access
Creating clustered index ucMSpublication_access
Creating table MSqreader_agents
Creating unique index ucMSqreader_agents
Creating table MSqreader_history
Creating clustered index ucMSqreader_history
Creating table MSrepl_backup_lsns
Creating clustered index ucMSrepl_backup_lsns
Creating table MSpublicationthresholds
Creating clustered index ucmspublicationthresholds
Creating table IHpublishers
Creating table IHpublishertables
Creating table IHarticles
Creating table IHpublishercolumns
Creating table IHcolumns
Creating table IHindextypes
Creating table IHpublisherindexes
Creating table IHpublishercolumnindexes
Creating table IHpublications
Creating table IHextendedArticleView
Creating table IHconstrainttypes
Creating table IHpublisherconstraints
Creating table IHpublishercolumnconstraints
Creating table IHsubscriptions
Creating table sysschemaarticles
Creating table MScached_peer_lsns
Creating view IHextendedSubscriptionView
Creating view syssubscriptions
Creating view syspublications
Creating view sysarticles
Creating view sysarticlecolumns
Creating view IHsyscolumns
Dropping all distribution stored procedures and functions that are now in resource or are obsolete
Dropping all distribution stored procedures and functions that are created locally
Creating 'fn_MSmask_agent_type'.
Creating 'sp_MSset_syncstate'.
Creating 'sp_MSadd_repl_commands27'.
Creating 'sp_MSadd_replcmds'.
Creating 'sp_MSremove_published_jobs'.
Creating 'sp_MSsubscription_cleanup'.
Creating 'sp_MSdelete_dodelete'.
Creating 'sp_MSdelete_publisherdb_trans'.
Creating 'sp_MSmaximum_cleanup_seqno'.
Creating 'sp_MSdistribution_delete'.
Creating 'sp_MSdistribution_cleanup'.
Creating 'sp_MShistory_cleanup'.
Creating 'sp_MSget_repl_version'.
Creating view MSdistribution_status
Creating 'sp_MSlog_agent_cancel'.
Adding user 'guest'.
Adding role 'replmonitor'.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
Msg 22538, Level 16, State 1, Procedure sp_MSrepl_check_job_access, Line 155
Only replication jobs or job schedules can be added, modified, dropped or viewed through replication stored procedures.[/font]
[font="Arial Black"]Any ideas about why this is happening? Could the fact that I'm using compatibility level 80 be part of the problem?[/font]
June 23, 2009 at 1:06 pm
The only thing I can think of is that there are some old Jobs that never got deleted from the previous set up of replication. Could you check if you have a job with name like "Replication agents checkup"? If you find this job, also check the entry for this job at msdb.dbo.sysjobs and msdb.dbo.sysjobsteps.
If it is present, you probably will have to remove this entry form the sysjobs, sysjobsteps and sysjobschedules in the msdb DB.
-Roy
June 23, 2009 at 4:52 pm
Thanks, Roy.
OK, I've removed all vestiges of the "Replication agents checkup" job,
but when I execute sp_adddistributiondb I still get the same error.
I notice that I still have jobs:
"Reinitialize subscriptions having data validation failures"
"Distribution clean up: Distribution"
"Expired subscription clean up"
Should I remove these too?
June 24, 2009 at 6:34 am
Yes, please do so... There should not be any replication jobs on the distributor when you set it up once again
-Roy
June 24, 2009 at 10:15 am
Thanks much, Roy - problem solved.
Carl
June 24, 2009 at 11:50 am
Glad I could help..:-)
-Roy
July 17, 2009 at 2:22 pm
Hi,
Today, I also have this error when I setup replication in sql 2005: Msg 22538, Level 16, State 1, Procedure sp_MSrepl_check_job_access, Line 155. I also found there is a job called "Replication agents checkup". Did you suggest to delete any rows related to Replication agents checkup in the tables sysjobs, sysjobsteps, and sysjobschedules? I am afraid that the delete of rows from these tables will cause new problems.
PC
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply