SQL Server Snapshot Replication - Error on Publication

Posted on 2013-10-07
Medium Priority
Last Modified: 2014-02-02
I have 2 databases sitting on the same 2008 R2 Sql Server and I am trying to replicate from one to another.  I have selected 2 tables and it is failing.  I keep getting: "Script failed for Table 'dbo.Agent Master'.

Can someone please help.  This seems like the simplest of tasks and I am not sure what I am doing wrong.

I attempted initially to use a domain user that have owner ship rights on both tables.  That created another error, so I went and used "Run under the SQL Server Agent server account", even though it is not recommended.  That got me to the above error.  The previous error was an authority issue.

Thanks in Advance, John
Question by:michd
  • 5

Author Comment

ID: 39553131
Here is a troubleshooting snapshop...

C:\Users\user>"c:\Program Files\Microsoft SQL Server\100\COM\snapshot.exe" -P
ublisher [XXXX-YYYYY] -PublisherDB [DBName] -Distributor [XXXX-YYYYY] -Publ
ication [Test] -DistributorSecurityMode 1
2013-10-07 17:15:38.28
2013-10-07 17:15:38.28 Microsoft (R) SQL Server Snapshot Agent
2013-10-07 17:15:38.28 [Assembly Version =, File Version = 10.50.1600.1 ((KJ_RTM).100402-1539 )]
2013-10-07 17:15:38.28 Copyright (c) 2008 Microsoft Corporation.
2013-10-07 17:15:38.28 The timestamps prepended to the output lines are expressed in terms of UTC time.
2013-10-07 17:15:38.28 User-specified agent parameter values:
2013-10-07 17:15:38.28 --------------------------------------
2013-10-07 17:15:38.28 -Publisher XXXX-YYYYY
2013-10-07 17:15:38.28 -PublisherDB DBName
2013-10-07 17:15:38.28 -Publication Test
2013-10-07 17:15:38.28 -Distributor XXXX-YYYYY
2013-10-07 17:15:38.28 -DistributorSecurityMode 1
2013-10-07 17:15:38.28 --------------------------------------
2013-10-07 17:15:38.28 Connecting to Distributor 'XXXX-YYYYY'
2013-10-07 17:15:38.33 Parameter values obtained from agent profile:
2013-10-07 17:15:38.33 ---------------------------------------------
2013-10-07 17:15:38.33 -BcpBatchSize 100000
2013-10-07 17:15:38.33 -HistoryVerboseLevel 2
2013-10-07 17:15:38.33 -LoginTimeout 15
2013-10-07 17:15:38.33 -QueryTimeout 1800
2013-10-07 17:15:38.33 ---------------------------------------------
2013-10-07 17:15:38.34 Connecting to Publisher 'XXXX-YYYYY'
2013-10-07 17:15:38.35 Publisher database compatibility level is set to 90.
2013-10-07 17:15:38.35 Retrieving publication and article information from the publisher database 'XXXX-YYYYY.DBName'
2013-10-07 17:15:38.36 [0%] Locking published tables while generating the snapshot
2013-10-07 17:15:38.40 [0%] Bulk copying snapshot data for article 'Agent Master'
2013-10-07 17:15:38.40 [0%] Bulk copied snapshot data for article 'Agent Master' (78 rows).
2013-10-07 17:15:38.40 [21%] Bulk copying snapshot data for article 'AgentMaster'
2013-10-07 17:15:38.41 [21%] Bulk copied snapshot data for article 'AgentMaster' (94 rows).
2013-10-07 17:15:38.57 [46%] Sorting article objects in the proper creation order
2013-10-07 17:15:38.57 [46%] Customizing object for scripting based on article 'Agent Master'
2013-10-07 17:15:38.63 [46%] Customizing object for scripting based on article 'AgentMaster'
2013-10-07 17:15:38.76 [46%] Resolving duplicate object names among replicated objects
2013-10-07 17:15:38.76 [46%] Analyzing foreign key references among article objects in the publication
2013-10-07 17:15:38.76 [46%] Analyzing external references of all replicated check and default constraints
2013-10-07 17:15:38.76 [46%] Analyzing dependencies on non-article objects
2013-10-07 17:15:38.76 [46%] Generating schema scripts for article 'Agent Master'
2013-10-07 17:15:38.78 [46%] The replication agent had encountered an exception.
2013-10-07 17:15:38.78 Source: Unknown
2013-10-07 17:15:38.78 Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException
2013-10-07 17:15:38.78 Exception Message: Script failed for Table 'dbo.Agent Master'.
2013-10-07 17:15:38.78 Message Code: Not Applicable
2013-10-07 17:15:38.78


Author Comment

ID: 39555258
I even tried it with a brand new empty file.  Fails.
LVL 35

Expert Comment

by:David Todd
ID: 39556671

table dbo.[agent master] appears to be the culprit. How about dropping that from the publication and concentrating on dbo.agentmaster.

I'm a bit suspicious of the apparent name clash between these two tables (and their publications) Strongly recommend to rename the table and publication to remove the space in the name. Suggest replacing it with an underscore.

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions


Author Comment

ID: 39558832
Seems to happen for both tables.

Accepted Solution

michd earned 0 total points
ID: 39695679
Feel free to cancel.

Author Closing Comment

ID: 39827458
Issue was resolved internally.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question