Replication Snapshot Fail “Microsoft.SqlServer.Management.Smo.FailedOperationException Exception”


Replication-Replication Snapshot Subsystem: agent failed. 

The replication agent had encountered an exception.  
Source: Unknown  Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException  Exception 
Message: Script failed for UserDefinedTableType .   Message Code: Not Ap  

--------------------------------------------------------------------------------------------
So here my Snapshot is failing with above error in error log.

My Configuration

Publisher Server: SQL 2008 R2
Publisher Database: SQL 2005 Compatibility(90)
Distributor: SQL 2008 R2
Distributor Database: SQL 2005 Compatibility(90)
Subscriber: SQL 2008 R2
Subscriber Database: SQL 2008 R2 Compatibility(100)


Scenario: 

I have Setup Snapshot replication between 2 servers both running on SQL server 2008 R2 and publisher database compatibility 2005 (90). While setup replication there are no issues reported.

Problem: 

When I tried to run snapshot job it was failing with error

 “The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information.”

So I quickly jump to error log and found error saying

Microsoft.SqlServer.Management.Smo.FailedOperationException  Exception 
Message: Script failed for UserDefinedTableType


If you check error log in grid format then only you will find detail error.

So message was clear  “Script failed for UserDefinedTableType” but object with specified name is not available then  try below query

/********************************************************/
“select * from  sys.types where name like '%table name%'”
/********************************************************/

Issue: 

So our database does have UserDefinedTableType which is not supported in SQL server 2005 to script out.


FIX: 

So now there is 2 was to fix snapshot replication job.

1. Change compatibility mode for publisher database to 100
2. Remove the user defined table from publisher database

So once one of fix is used, Snapshot replication will generate data.

Note: 

Keeping distribution database compatibility different from publisher does not impact snapshot job.

1 comment:
Write comments
  1. Superb i really enjoyed very much with this article here. Really its a amazing article i had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.

    SEO Company in India

    Digital Marketing Company in India

    ReplyDelete

Please do not enter spam links

Meet US

Services

More Services