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.