SQL Server Performance

Dynamic snapshot Job not Executing

Discussion in 'Performance Tuning for SQL Server Replication' started by ndkrishna_1981, Mar 1, 2007.

  1. ndkrishna_1981 New Member

    Hai,

    I have created a Job for doing Dynamic snapshot (merge replication). The job is not executing and it is displaying the following Error:

    *"The login '5' does not have access permission on publication 'LoanProducts' because it is not in the publication access list.
    (Source: KRISHNA (SQL-DMO); Error number: 21049)"

    '5' is the database ID

    'LoanProducts' is the publication name.

    *regards,
    Krishna Kumar*

    Plz see my code below:

    SET @STEP = 'Run agent - LoanProducts'
    SET @CMD = '-Publisher [' + @Publisher + '] '
    + '-PublisherDB [' + @PublisherDB + '] '
    + '-Distributor [' + @Publisher + '] '
    + '-Publication [LoanProducts] '
    + '-ReplicationType 2 '
    + '-DistributorSecurityMode 1 '
    + '-DynamicFilterLogin [' + @DBID + '] '
    + '-DynamicSnapshotLocation [' + @UNCROOT + 'LoanProducts]'
    PRINT 'Adding step ''' + @STEP + ''''
    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
    @job_id = @JobID,
    @step_id = 6,
    @step_name = @STEP,
    @command = @CMD,
    @database_name = 'Distribution',
    @server = @Publisher,
    @database_user_name = '',
    @subsystem = 'Snapshot',
    @cmdexec_success_code = 0,
    @flags = 0,
    @retry_attempts = 10,
    @retry_interval = 1,
    @output_file_name = '',
    @on_success_step_id = 0, @on_success_action = 3,
    @on_fail_step_id = 0, @on_fail_action = 2
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  2. dineshasanka Moderator

    did you created job manually, or it created fromt the replication
    Also, check the permissions

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  3. satya Moderator

    True, the permissions for the user and for SQLAgent too.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. MohammedU New Member

    Right the 'LoanProducts' publication and click properties...
    Click on "ublication Access List" and add the loging to get the publication access and then try...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page